If you find yourself with data spread across different cells that needs merging, there exist several helpful functions at your disposal. Among these are the CONCATENATE function in Google Sheets as well as the JOIN function.
Within this tutorial, I shall provide illustrations of employing the CONCATENATE function within Google Sheets.
Before delving into the examples, let’s first grasp the concepts of the concatenate operator and the concatenate function.
The Google Sheets CONCATENATE Operator
The ampersand sign (&) functions as the concatenate operator, facilitating the combination of cells in Google Sheets.
For instance, consider having the initial name and last name displayed as follows:
A formula like the following can be employed to merge the first and last names:
=A2&” “&B2
It’s worth noting that a space character has been interposed between the references to the first and last names. This choice allows for the separation of these words with a space character. Should you desire a different separator (also known as a delimiter), options such as commas, hyphens, and semi-colons are available.
Though the ampersand-based concatenation is effective, it proves most valuable when dealing with a small number of cells. For scenarios requiring the merging of numerous cells in Google Sheets, opting for functions would be more practical.
Google Sheets CONCATENATE Function Syntax
The Concatenate function in Google Sheets offers a swift way to unite cell values.
The syntax for the Concatenate function is as follows:
CONCATENATE(string1, [string2, …])
string1 – This denotes the initial string (first string).
string2 – This signifies the second string to be merged with the first. You can specify additional strings in a similar manner.
Let’s now explore a couple of instances showcasing the application of the CONCATENATE function in Google Sheets.
Example 1 – Combining Google Sheets Data with a Space
Suppose you possess a dataset containing first names and last names, and your aim is to concatenate these elements while inserting a space in between.
Here’s how to proceed:
1. Enter =CONCATENATE( into the desired cell.
2. Input the first string, A2 in this context.
3. Specify the delimiter (separator) within double quotation marks. In our case, a single space is preferred, as in: ” “.
4. Enter the source for the second string, B2 in this instance.
The resulting formula would appear as:
=CONCATENATE(A2,” “,B2)
Observe that the function employs three arguments: the first name, a space character enclosed in double quotes, and the second name.
Concatenating Entire Columns
Once the formula resides in the initial cell, you can employ the fill handle to extend its application throughout the column.
Example 2 – Google Sheets Concatenation with a Comma Separator
Imagine you possess the same names dataset, but instead of a space separator, you intend to structure it as last name, first name.
Here’s an example formula to achieve this in Google Sheets:
=CONCATENATE(B2,”, “,A2)
Example 3 – Merging First and Last Names in Google Sheets with Sequential Numbers
The Concatenate function proves beneficial even when merging text with sequential numbers.
For instance, within the following dataset, the objective is to prepend each name with a running number. Consequently, the first name would read as “01 – John Spike,” the second name as “02 – Brad Connor,” and so forth.
Here’s the formula to accomplish this:
=CONCATENATE(ROW()-1,” – “,A2,” “,B2)
Example 4 – Concatenation in Google Sheets Using the IF Function
In order to comprehend the process of utilizing the IF function to concatenate strings in Google Sheets, it’s essential to grasp the IF function itself. The syntax for this function is as follows:
IF(logical_expression, value_if_true, value_if_false)
logical_expression – An expression or a reference to a cell containing an expression representing a logical value (TRUE or FALSE).
value_if_true – The output when logical_expression evaluates to TRUE.
value_if_false – The output when logical_expression evaluates to FALSE. This argument is optional.
To employ CONCATENATE with the IF function, one can embed CONCATENATE within one of the value_if arguments.
Consider the following example where concatenation should only occur if the value in column A is less than 5:
Here’s how to construct the formula:
1. The logical_expression to test is whether the value in A1 is greater than 5, expressed as A1>5.
2. “NOPE” is designated as the value_if_true.
3. The value_if_false involves a CONCATENATE function that merges cells B1, a comma and space, and C1 via the expression CONCATENATE(B1,”, “,C1).
4. Subsequently, the formula is dragged down the column over the appropriate cells.
Alternatives to the CONCATENATE Function
Utilizing the CONCAT Function in Google Sheets
The CONCAT function in Google Sheets operates similarly to CONCATENATE, but exclusively accommodates two text strings rather than three or more. Any instances requiring just two arguments could employ the CONCAT function instead. While CONCAT offers certain advantages, it’s often prudent to stick with CONCATENATE due to its greater versatility.
Joining a Cell Array with the JOIN Function
Suppose you’re working with a dataset akin to the one illustrated below, and your goal is to combine these names within a single cell, with each name on a new line. To achieve this, the JOIN function comes into play, following this syntax:
JOIN(delimiter, value_or_array1, [value_or_array2, …])
delimiter – The character or string placed between each value.
value_or_array1 – The initial value.
value_or_array2 – Additional value(s) or an array to be appended using the specified delimiter.
The ensuing formula serves this purpose:
=JOIN(char(10),A2:A6&” “&B2:B6)
Take note that this is an array formula, requiring the use of Control + Shift + Enter for execution. The CHAR(10) element introduces line breaks to the formula’s output. This approach is also practical when dealing with addresses scattered across cells (e.g., house number, street name, city name) that need consolidation for generating address labels.
Concatenating Beyond 2 Columns in Google Sheets Using the & Operator
While the CONCATENATE formula might become unwieldy with numerous arguments, the & operator can be employed repeatedly. Ensure the operator is positioned on either side of the central arguments, as demonstrated in the subsequent example.
FAQ: Combining Text in Google Sheets
How to Employ CONCATENATE in Google Sheets
The procedure involves:
1. Typing =CONCATENATE( in an empty cell.
2. Providing the cell reference for the first value, followed by a comma.
3. Inserting a delimiter enclosed in double quotes. For instance, for a space, input ” “, followed by a comma.
4. Specifying the cell reference for the second text string.
5. Pressing Enter.
A complete CONCATENATE function might resemble:
=CONCATENATE(A2,” “,B2)
Merging Two Columns in Google Sheets
Apply the previously mentioned steps to concatenate a row in the columns, then extend the action using the fill handle or AUTOFILL for the entire column.
Locating the Concatenate Function in Google Sheets
Enter =CON
CATENATE into an empty cell, or navigate through Insert > Function > All > CONCATENATE to locate string concatenation in Google Sheets.
Concatenating Three Columns in Google Sheets
The CONCATENATE function in Google Sheets isn’t confined to just two columns; you can include three or more arguments without any issue. To merge three columns with spaces in between, consider a formulation like:
=CONCATENATE(B1,” “,C1,” “,D1)
Troubleshooting the Concatenate Formula
Ensure accurate cell references.
Verify the presence of the desired delimiter enclosed in double quotes. For instance, use ” ” for a space.
Understanding Concatenation
In essence, concatenation involves the joining of text strings within Google Sheets. Remember, when incorporating separators between values, utilize delimiters enclosed in double quotes. In the above instance, a space is implemented as the delimiter, presented as ” “.
Embrace the Process of Concatenation
The examples presented above offer a substantial introduction to these valuable tools within Google Sheets. Should you require further information or possess inquiries, don’t hesitate to share in the comments section.