What is the CONCATENATE function? How to use the CONCATENATE function in Excel and Google Sheets.

The CONCATENATE function is one of the most commonly used text functions in Excel and Google Sheets. To find out what this function is, its formula, and how to use the CONCATENATE function in Excel and Google Sheets, please read the article below for more information.

Note: The CONCATENATE function works on Excel 2016, Excel 2007, 2010, 2013. From Excel 2016 and later versions, and in Excel Mobile and Excel Web, this function has been renamed to CONCAT function (with the same formula and application as the CONCATENATE function).

1. What is the CONCATENATE function? Formula and applications in Excel

The CONCATENATE function in Excel is a text function used to combine text strings or merge values from multiple cells into one cell.

The CONCATENATE formula in Excel is as follows: =CONCATENATE (text1, [text2],…)

Where:

• text1: is a required parameter, which can be text values, numbers, or cell references.
• text2,…: are optional additional parameters. You can concatenate up to 255 data parameters, equivalent to 8192 characters.

Things to note when using the CONCATENATE function:

• The CONCATENATE function in Excel requires at least one argument to be text to work.
• The result of the CONCATENATE function is always a text string even if all source values are numbers.
• The CONCATENATE function in Excel does not recognize arrays, so you need to list each cell reference.
• If the arguments of the CONCATENATE function are not valid, the formula will return an error #VALUE!.

2. How to use the CONCATENATE formula in Excel

Option 1: Enter the formula directly

Step 1: Select the cell where the combined data result will be displayed.

Step 2: Enter the syntax “=CONCATENATE(“.

Step 3: Click the mouse cursor to select the first data reference cell, and the subsequent reference cells will be separated by commas.

Note: If the data is a text string, you need to put it in double quotation marks.

For example: =CONCATENATE(A1, ” “, B1, ” completed”)

Step 4: After entering all the data to be combined, close the parentheses to complete the formula and press ENTER to display the result.

Option 2: Use the CONCATENATE formula from FORMULAS

Step 1: Select the cell where the combined data result will be displayed.

Step 2: On the Ribbon toolbar, click the FORMULAS tab, select the Text Function button, and then select the CONCATENATE function.

Step 3: The CONCATENATE formula dialog box will appear. You can select values from cells in the worksheet or enter data into each text1, text2 cell and click OK to display the result.

Concatenate values from two or more cells into one cell

For example, in a worksheet, you need to use the CONCATENATE function to combine First Name and Last Name. To combine the values of cells A2 and B2 in the worksheet, you can use the simplest concatenation formula: =CONCATENATE(A2, B2).

If you want to concatenate the values of multiple cells, you need to list each cell reference separately. For example: =CONCATENATE(A11, B11, C11).

With this formula, the values will be combined without any separators, as shown in the example below.

Concatenate cells with spaces, commas, and other delimiters

If you want to separate the concatenated values with spaces, commas, or other punctuation marks when using the CONCATENATE function, you can use the following formulas:

• Concatenate values with spaces: =CONCATENATE(A2, ” “, B2).
• Concatenate values with commas: =CONCATENATE(A3, “, “, B3).
• Concatenate values with hyphens: =CONCATENATE(A4, ” – “, B4).

For example, when combining the First Name and Last Name values with spaces, commas, and hyphens:

Concatenate text strings with cell values

The CONCATENATE function in Excel can not only concatenate cell values but also concatenate cell values with a text string.

For example, the following worksheet requires using the CONCATENATE function to concatenate Academic Year, Course Code, and Class Number to determine the Course ID and add “Completed” after the Course ID to clarify the enrollment status.

In this case, you can use the CONCATENATE function with the following formula:

=CONCATENATE(A2, ” “, B2, “-“, D2, ” “, “Completed”).

In addition, you can also add a text string at the beginning or in the middle of the CONCATENATE formula as additional examples. Note that text string data must be placed in double quotation marks for the function to work.

Concatenate text strings with another formula

You can also use the CONCATENATE function to concatenate a text string with a calculated value from another formula.

For example, you can use the following formula to display the current date on the Excel sheet:

=CONCATENATE(“Today is “, TEXT(TODAY(), “dd-mmm-yy”)).

The formula result will be displayed as shown below:

Concatenate text strings with line breaks

Usually, you will separate the text strings with spaces, as in the examples above. However, if you need to separate the values with line breaks to make the data result more readable and presentable.

However, you cannot type line breaks in formulas using the CONCATENATE function. Therefore, you need to use the CHAR function with the corresponding ASCII code number to display the line breaks. Remember the following 2 CHAR functions to perform this:

• For Windows, use CHAR(10).
• For MAC, use CHAR(13).

For example, use the CONCATENATE function to concatenate the values of Name, Address, and Phone Number to create complete customer information (used for sending/receiving goods, letters) in the following worksheet.

In this case, the CONCATENATE formula would be as follows:

=CONCATENATE(A2, ” “, B2, CHAR(10), C2, CHAR(10), D2).

3. How to use the CONCATENATE function in Google Sheets

The formula of the CONCATENATE function in Google Sheets is: =CONCATENATE(string1, [string2,…]).

Where: string1, string2,… are the data strings.

Unlike the CONCATENATE function in Excel that can only select individual data cells, the CONCATENATE function in Google Sheets can select a data string as a cell or a range of data.

To use the CONCATENATE function in Google Sheets, follow the steps below:

Step 1: In the spreadsheet, select the cell where the result of the formula will be displayed.

Step 2: Hover over the Insert tab, then move the mouse cursor over the Formula button, and select the CONCATENATE function.

Step 3: The CONCATENATE formula dialog box will appear. Proceed to select values from cells in the worksheet or enter data into each string1, string2 cell and click OK to display the result.

The data reference of the CONCATENATE function in Google Sheets is separated by semicolons “;”. For string data, you need to place it in double quotation marks.

Step 4: After selecting all the data values, close the parentheses and press ENTER to display the result.

In addition, you can also select the function quickly by entering the “=CONCATENATE(” content and selecting the function from the Google Sheets suggestions.

In general, the way to use the CONCATENATE function in Google Sheets is similar to how to use the CONCATENATE function in Excel, so you can refer to the examples in Excel above for implementation.

Final Thoughts

Now you know what the CONCATENATE function is. Hopefully, the examples presented by FPT Shop above will help you understand how to use the CONCATENATE function in Excel and Google Sheets for easy implementation. If you have any further questions about the CONCATENATE function in Excel, please comment below for discussion with others.

• Guide on how to convert numbers to words in Excel quickly and conveniently
• Guide on how to use the DAY function in Excel, with easy-to-understand examples