What is Concatenate in Excel?
Concatenate in Excel is joining two strings into one continuous string. You can join two strings using the CONCATENATE function or the “&” (ampersand) operator.
This guide shows how to use these two methods with examples.
How to Concatenate Columns in Excel
The image above shows two columns, A and B. Column C shows how to concatenate the columns using CONCATENATE function. Column D shows how the columns are combined using the “&” (ampersand) Operator.
In this section, I will demonstrate how to combine column A and column B using CONCATENATE or “&” (ampersand) Operator.
How to Concatenate in Excel with the CONCATENATE Function
The syntax of CONCATENATE Function is:
CONCATENATE(text1, [text2], [text3])
text1, text2 and text3 are the different strings you wish to combine.
Refer to the image above. In this example, I want to combine cells A and B to form the sentence “The Number of cellA are cellB”. The formula for cell C4 is shown below:
=CONCATENATE("The Number of", " ",A4, " ", "are"," ", B4)
Concatenate in Excel using the CONCATENATE function is simply adding the strings you wish to combine within the bracket “()”. Each string is then followed by a comma (,).
To add an empty space, use ” ” – quote marks with space between them.
How to Concatenate in Excel with the “&” (ampersand) Operator
To concatenate with “&” (ampersand) Operator:
On the cell you want to combine the columns, enter the equal to sign (=), followed by “&”. Unlike in CONCATENATE, with ampersand (&) each value is separated by & instead of a comma (,).
Here is the formula for cell D4:
="The Number of" & " " & A4 & " " & "are" &" " &B4
To add an empty space use double quotes with a space.
How to Concatenate in Excel (Adding Space, Comma and Other Characters)
In the last section I demonstrated how to concatenate two columns. In this section, I will demo how to add commas, spaces and other special characters.
If you have read this guide to this point, you will notice that when I included a text string or space, I used double quotes around them.
To add an empty space, simply include two quotes with a space, ” “. You can also use the same method to include a comma or any other character.
As an example, to combine the two texts below separated by a comma and a stop:
This is how you add, followed by this.
Use the CONCATENATE formula below:
=CONCATENATE("This is how you add",",", " ", "followed by this",".")
You can achieve the same result using the & operator formula shown below:
="This is how you add" & "," & " " & " " & "followed by this" & "."
Here are the results in Excel:
More Examples of Excel Concatenate
In the last section of this guide I will show one more example.
Refer to the image above. The worksheet contains products and their prices. In the example, I combined columns A and B in C with the formula below:
=CONCATENATE(A3, " ", TEXT(B3, "$#,#0.00"))
The cells are combined with CONCATENATE. But the second cell, B3 is formatted with the TEXT function. TEXT formats the numbers in column B to include the $ sign. It also adds 2 decimal places to the numbers.
Concatenate in Excel is very useful if you want to join 2 strings into 1. In this guide I have demonstrated different ways to do this. I hope you found it useful.
If you have any question or comment use the “Leave a Reply” form at the end of the page. You could also share your experience with Excel concatenate.
Other Helpful Guides
- How to Convert Text to Number in Excel in 4 Easy Ways
- Excel Substring: How to Get (Extract) Substring in Excel