What does it do?
Joins two or more text strings into one string. The item can be a text value, number, or cell reference.
=CONCATENATE(text1, [text2], [text3], …)
What it means:
=CONCATENATE(the first text, the second text, and so on…)
Excel’s CONCATENATE functions joins two or more text strings into one string. The item can be a text value, number, or cell reference.
If you add a double quotation with a space in between ” ” then this will add a space between the texts selected on either side.
You can also add a line break in between each text string. This is done by entering the CHAR(10) function in between each text string/argument. You will then need to select WRAP TEXT in order to see each text on a separate line.
See how easy this is to implement this by using employee data on the example below.
STEP 1: We need to enter the CONCATENATE function in a blank cell:
STEP 2: The CONCATENATE arguments:
text1, [text2], [text3], …
Which text do you want to join together?
Let us select all the columns:
=CONCATENATE(A12, B12, C12, D12)
Now let’s add the function CHAR(10) to add a line break between each text
=CONCATENATE(A12, CHAR(10), B12, CHAR(10), C12, CHAR(10), D12)
Apply the same formula to the rest of the cells by dragging the lower right corner downwards.
STEP 3: Go to Home > Alignment > Wrap Text to show the text in multiple lines and you now have all of results!
How to Use the Concatenate Formula in Excel