What does it do?

Joins two or more text strings into one string. The item can be a text value, number, or cell reference.

Formula breakdown:

=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:


Concatenate Formula

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)

Concatenate Formula


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)

Concatenate Formula

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 Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn