Excel concatenate functions

 

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!

How to Use the Concatenate Formula in Excel

Excel concatenate functions

HELPFUL RESOURCE:

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Cleaning Data with Excel’s PROPER Formula   What does it do? Capitalizes the first letter in a text string and any other letters in the text that follow a space. Converts all other letters to lowercase letters. Formula breakdown: =PROPER(text) What it means: =PROPER(this text cell) There are...
SUMIF Function: Introduction   What does it do? Sums the values in a range that meet a criteria that you specify Formula breakdown: =SUMIF(Range or Cells, Criteria, ) What it means: =SUMIF(Evaluate this Range/Cells, With this Criteria, ) The SUMIF function is used widely amon...
Named Ranges with Vlookup Formula What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP...
Cleaning Data with Excel’s SUBSTITUTE Formul... What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, By this text character, To this text character, ) When you needed to replace a specific text...