10.8. Use of common functions (SUM, AVERAGE, etc.): Concatenation of strings with the CONCATENATE function

Basic Functions: SUM and AVERAGE

Before we dive into string concatenation, it's important to understand some of the basic functions of Google Sheets that are often used for numerical calculations.

• SUM: The SUM function is used to add a set of numbers or a range of cells. For example, =SUM(A1:A10) will add all numbers from cell A1 to A10.
• AVERAGE: The AVERAGE function calculates the arithmetic mean of a set of numbers. If you want to calculate the average of the values ​​in cells B1 through B10, you would use =AVERAGE(B1:B10).

These functions are fundamental to any type of numerical analysis and are often used in conjunction with other functions to gain deeper insights into data.

Concatenate Strings with CONCATENATE

String concatenation is the process of combining two or more strings of characters to form a single string. In Google Sheets, this is often necessary when you want to join information from different cells or add text to numeric data to make it more readable.

The CONCATENATE function is one of the ways to concatenate strings in Google Sheets. The basic syntax is =CONCATENATE(string1, string2, ...), where string1, string2, etc., are the strings you want to combine.

Example of Using the CONCATENATE

Let's assume you have a list of first names in one column and last names in another, and you want to create a column with full names. If the names are in column A and the surnames are in column B, you could use the CONCATENATE function as follows:

=CONCATENATE(A2, " ", B2)

This will combine the first name in cell A2 with the last name in cell B2, separated by a space. If you drag this formula down, you can apply it to the entire list to get full names in each row.

Alternatives to the CONCATENATE

While the CONCATENATE function is useful, Google Sheets also offers other ways to accomplish the same task. One of them is the ampersand operator (&), which can be used to join strings more concisely:

=A2 & " " & B2

Another alternative is the CONCAT function, which is a shorter version of CONCATENAR and accepts only two arguments:

=CONCAT(A2, B2)

Also, for situations where you need to join a series of values ​​with a common delimiter, such as a comma-separated list, you can use the TEXTJOIN function. This function is particularly useful because it allows you to specify a delimiter and decide how to deal with empty cells:

=TEXTJOIN(", ", TRUE, A2:A10)

This will create a string with all values ​​in the range A2:A10, separated by commas, and will ignore any empty cells if the second argument is TRUE.

Considerations when Concatenating Strings

When working with string concatenation, it is important to consider the data format. For example, if you are concatenating numbers with text, you may need to use the TEXT function to convert numbers to strings and format them accordingly:

=CONCATENATE("R$", TEXT(C2, "0.00"))

This will convert the number in cell C2 to a string formatted as a currency value and concatenate it with the currency symbol "R$".

Conclusion

Google Sheets offers a variety of functions for working with numeric and textual data. The CONCATENAR function and its alternatives, such as the & operator and the CONCAT and TEXTJOIN functions, are extremely useful for manipulating strings. By mastering these functions, you will be able to create more dynamic and informative spreadsheets, capable of presenting data clearly and efficiently.

Remember that practice makes perfect. Try these functions with your own datasets and see how they can simplify your work and improve the presentation of your information.