Updated May 31, 2023
Introduction to Concatenate Strings in Excel
Concatenate Strings in Excel combine values from several cells in one cell or join different pieces of text in one cell. This function is mostly used where data is not structured in Excel, and we want to combine the data of two or more columns in one or a row. Concatenate is very helpful in structuring the data according to our requirements. It combines strings and text and is a built-in function in Excel. Concatenate is replaced as CONCAT from Excel 2016 and newer versions. It is known as the Text/String function as well.
Concatenate can combine up to 30 text items to make one text.
Syntax:
How to Use Concatenate Strings in Excel?
There are multiple ways to use the concatenate function. We will take a look at them with the help of some formulas.
Let’s see an example below where we need to join the employees’ first and last names using the concatenate function.
Example #1 – CONCATENATE using Formula Tab in Excel
We have two columns of first and last names in the image below.
Now we would join their first and last names to get the complete names using the concatenate function. Let’s see the steps to insert the Concatenate Function to Join the First and Last Names.
Go to Column C2.
Select the Formulas Icon and click on Insert Function, as shown below.
Then select a Category as “TEXT” and then select the Function category will open in which you can choose Function CONCAT and Click OK.
A window will open, as shown below, after clicking OK, where you can type the text you want to concatenate.
For column C2, in-text 1 references cell B2, which is the Last Name, Text 2-Put Commas inside semicolons, and Text 3, references cell B1, which is the first name.
Click on the OK button.
The last name followed by the first name will be concatenated in column C2.
Drag down the formula till the end of the data, and the formula will be applied to the rest of the data.
Example #2 – CONCATENATE Using Direct Formula
As you can see in the below screenshot, we need to concatenate the text in Column A (First Name), Column B (Last Name), Column C (Address), Column D (City), and Column E(Zipcode) to arrive at the Mailing address in Column F.
We can do this by simply entering the function in cell F6.
Steps for Entering the Concatenate Function
- Type “=CONCATENATE(“ in cell H6.
- Now give the reference of cell B6 for Text1.
- Enter space in between the semicolons for Text 2.
- Give the reference of cell C6 for Text3.
- Enter comma”,” in between the semicolons for Text 4.
- Give the reference of cell D6 for Text5.
- Enter comma”,” in between the semicolons for Text 6.
- Give the reference of cell E6 for Text7.
- Enter dash”-” in between the semicolons for Text8.
- Give the reference of cell F6 for Text9.
Press Enter Key after entering the formula.
Drag down the formula in the below cells to get the mailing address details of other rows.
As you can see in the above screenshot, we have the mailing address in column H for all the persons.
Example #3 – CONCATENATE Using “&” Operator
The “&” operator instead of concatenate function is the way to concatenate. Let us take the same example we used in Example 1, where we want to join the first and last names.
To concatenate the text strings in columns A and B with the help of the “&” operator, we must follow the steps below. Go to cell C2 and give a cell reference to cell B2, as shown in the below screenshot.
Now Insert the “&” operator, comma, and space between the semicolons”, “as shown in the screenshot below.
Now again, Insert the “&” operator and give the reference of cell A2 and close the bracket.
Press the Enter Key, and you will get the desired result.
Drag the formula till the end of the data.
Example #4 – CONCATENATE Using Calculated Field
We can concatenate the text string even by calculating a certain field. Suppose you have the data of a few customers, and you need to enter certain comments for those customers, as given in the below screenshots.
The comment we need in column D is, “The invoice of certain $ value was due on this Month”. We need to follow the steps below to get such comments in column D. Go to cell D2 and Enter CONCATENATE function as below.
Now Insert the Comment “The invoice of $” between the semicolon as Text1.
Now give the reference of cell C2, which is the invoice amount value for Text2.
Comment “was due on” with one space between the semicolon as Text 3.
Give the cell reference of cell B2, which is the invoice due Month as Text4. Close the bracket.
Press the Enter Key to see the final comment in cell D2 below.
Drag down the formula for the rest of the data.
Things to Remember About Concatenate Strings in Excel
- Concatenate function requires at least one argument to work.
- From Excel 2016 or Newer Version, CONCATENATE is replaced with CONCAT. The Concatenate function will still work as it is kept for backward compatibility. However, we don’t think it will be kept in future versions, so it is highly recommendable to use CONCAT instead of CONCATENATE.
- The result of the concatenate function is always a Text String. Even if you use numbers to concatenate, the result will always be a text string.
- In Concatenate, each cell reference needs to be listed separately; it doesn’t recognize arrays.
- In CONCATENATE Function, you can use 8192 characters which means you can concatenate up to 255 Strings.
- The formula will give #Value! Error if anyone’s argument is invalid.
- The difference between the concatenate function and using the “&” operator to concatenate text string is that there is no limitation of 255 strings while using the “&” operator. Apart from that, there is no difference.
Recommended Articles
This is a guide to Concatenate Strings in Excel. Here we discuss how to use concatenate strings in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles to learn more–
