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.

DOWNLOAD EXCEL WORKBOOK

 

 

STEP 1: We need to enter the CONCATENATE function in a blank cell:

=CONCATENATE(

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!

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

3

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin