The name CONCATENATE means to combine or to join. The Excel CONCATENATE function combines or joins text located in different cells to one cell.
Microsoft introduced the CONCAT function to replace CONCATENATE in 2016. However, CONCATENATE will remain active in later versions for compatibility issues. The best practice is to use the CONCAT function since Microsoft may stop supporting CONCATENATE soon.
In this Excel tutorial, I will guide you on using the CONCATENATE function with syntax and examples.
How to use Excel CONCATENATE Function
Simply type =CONCATENATE(A2,B2,C2) in an empty cell. Note replace A2, B2, and C2 with your own cells.
Steps
1. Choose an empty cell where you wish to combine the cells.
2. Type =CONCATENATE(
3. Now choose a cell with the first text that you wish to combine.
4. Enter a comma to separate the text, then choose the second cell containing
5. If you wish to combine more cells, continue with the same trend
6. Lastly, enter the closing Parenthesis) and then press enter, So the final formula should look like this =CONCATENATE(A1,A2, A3)
Syntax
=CONCATENATE(text1,text2,[text3],…)
What it means
=CONCATENATE(first text, second text , third text, and so on)
Arguments
=CONCATENATE – This is the function name
=Text1, – The first text that you wish to combine
Text2, -(optional) The second text that you wish to combine with the first text
Text3, – (Optional) Refers to the third cell you wish to combine. This is optional. Note you can combine more than 3 cells and a maximum of 255 cells.
Important notes
- It’s important to note that you will lose formatting after combining the. cells
- You need to enter at least one value.
- The result is always a text string, even if you combine numbers only.
- If you use invalid arguments, Excel will display the #Value error.
Excel Data
| A | B | C |
| Mr | John | Kruger |
| 56 |
How to CONCATENATE two cells with a space
Copy and paste the data above in Excel for the purpose of this example
Two concatenate two cells or two columns with a space, you have to wrap the space using quotation marks.
=CONCATENATE(A2,” “,B2) will result to A B
=CONCATENATE(A3, ” “, B3, ” “,C3) will result in Mr John Kruger
How to concatenate two cells with a comma
The comma is used in separating values in the concatenate function. For this reason, many users become confused when they wish to combine data containing commas. Here is how to combine two columns with a comma.
For example
=CONCATENATE(A2,” ,”,B2) will result to A,B
=CONCATENATE(A3, “,”, B3, “,”,C3) Will result to Mr, John, Kruger
To combine two cells with a comma, you need to wrap the comma inside quotation marks. Also, if you wish to add any other character apart from a comma, you do it using quotation marks.
How to concatenate columns in Excel
Step 1: Type =concatenate( then Select the first cell that you wish to combine
Step 2: Select the cells that you wish to combine, then type the closing parenthesis) then press enter
=CONCATENATE(A2,B2,C2)
Step 3: Drag the formula down to the rest of the cells to concatenate the two columns to the end
How to concatenate Text with Line breaks
Line breaks help users to break the text and continue to the next line. It basically puts an end to the current line to start a new line. On normal occasions, you would just need to press Alt + Enter for Excel to start a new line.
You may need to put a line break when combining different texts from different cells or columns. In this case, you simply need to add CHAR(10)
To Join text with line breaks, follow these simple steps
Step 1: Type =CONCATENATE( in an empty cell
Step 2: Select the the texts that you wish to join together one by one separating them with commas. (the concatenate arguments ie text1, text 2 text 3)
=CONCATENATE(A2,B2,C2)
Step 3: Press enter and apply the same formula to the rest of the cells. You can apply the formula to the rest of the cells by dragging it down.
Step 4: Wrap text by navigating to Home tab> Alignment >Wrap Text
Step 5: After wrapping the text now it’s time to add a line break using the CHAR(10) function. Add it in between the texts that you wish to break.
=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2)
Step 6: Drag the formula to the rest of the cells to apply the line breaks
That’s how to join cells with line breaks using CHAR(10) in Excel.
Download EXCEL CONCATENATE FUNCTION TEMPLATE
How to CONCATENATE cells with Line break using the ampersand function
Step 1: First, Wrap text
Step 2: Type in the following formula and press enter
=A5&CHAR(10)&B5&CHAR(10)&C5
CONCATENATE Function alternatives
CONCAT()
CONCAT function is the best alternative for the CONCATENATE function, especially if you are dealing with the latest Excel versions. I,e Excel 2016 and later versions.
=CONCAT(A1:D1) is same as =CONCATENATE(A1:D1)
Ampersand operator (&)
You can use the Ampersand operator to achieve the same results as the CONCATENATE. Its more flexible and makes formulas short and clean
For instance =A2&B2 is same as =CONCATENATE(A2:B2)