Excel Concatenate Strings (Table of Contents)
Introduction to Concatenate Strings in Excel
Concatenate in Excel is used to 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 where we want to combine the data of two or more columns in one column or a row. Concatenate is very helpful in structuring the data according to our requirements. It combines strings and text together and is a built-in function in excel. Concatenate is replaced as CONCAT from Excel 2016 and newer versions. It is known as Text/String function as well.
Concatenate can join up to 30 text items and combine it 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 first and last names of employees by using concatenate function.
Example #1 – CONCATENATE using Formula Tab in Excel
In the below image, we have two columns of first and last name.
Now we would join their first and last name to get the complete name by using concatenate function. Let’s see the steps to insert the Concatenate Function to Join the First and Last Name.
Go to Column C2.
Select Formulas Icon and click on Insert Function as shown below.
4.9 (3,313 ratings)
View Course
Then select a Category as “TEXT” and then select Function category will open in which you can select CONCAT Function 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 gives reference to cell B2 which is the Last Name, Text 2-Put Comma inside semicolons and in Text 3 give reference of 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 for 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 Text2.
- Give the reference of cell C6 for Text3.
- Enter comma”,” in between the semicolons for Text4.
- Give the reference of cell D6 for Text5.
- Enter comma”,” in between the semicolons for Text6.
- 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 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 way to concatenate is using “&” operator instead of concatenate function. Let us take the same example that we used in Example 1 where we want to join the first name and the last name.
To concatenate the text strings in column A and B with the help of “&” operator, all we need to do is to follow the below steps. Go to cell C2 and give cell reference of cell B2 as shown in the below screenshot.
Now Insert “&” operator and comma and space in between the semicolons”, “as shown in the below screenshot.
Now again Insert “&” operator and give the reference of cell A2 and close the bracket.
Press 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 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 which we need in column D is something like this “The invoice of certain $ value was due on this Month”. To get such comments in column D we need to follow the below steps. Go to cell D2 and Enter CONCATENATE function as below.
Now Insert the Comment “The invoice of $” in between the semicolon as Text1.
Now give the reference of cell C2 which is invoice amount value for Text2.
Give the comment “was due on” with one space in between the semicolon as Text3.
Give the cell reference of cell B2 which is the invoice due Month as Text4. Close the bracket.
Press Enter key and You can see the final comment in cell D2 as 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 the CONCAT The Concatenate function will still work as it is still kept for backward compatibility. However, we don’t 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 be always in the form of 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 argument is invalid.
- The difference between the concatenate function and using “&” operator to concatenate text string is that there is no limitation of 255 strings while using “&” 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 along with practical examples and downloadable excel template. You can also go through our other suggested articles –
