How to CONCATENATE Using Microsoft Excel

By Carol J. Dunlop

Last Updated October 23, 2019

You can’t run your business without using data in some form or another. Emails, social media handles, names, phone numbers, and project names are just a few examples.

  • How would you like to create an outreach tweet to hundreds of influencers?
  • Or share your YouTube videos to specific users?

Microsoft Excel gives you the power to manipulate the data so you can use it effectively.

In this post, I’ll show you 5 different ways to concatenate data in Excel to meet your desired needs.

What does CONCATENATE mean?

First, concatenate is a function in Excel that means “to combine” or “to join together.” Using the concatenate function, you can combine text from different cells into one cell or split the contents from one cell into 2 cells. Using the CONCATENATE function allows you to join up to 30 items. Sounds simple enough. Here’s how to do it.

Note: Excel 2016 versions use the CONCAT function, which produces the same result as CONCATENATE.

Example #1: Creating a text string from several input cells

In this example, you’ve created a list of Twitter handles that you’d like to send your latest article.

  1. Select the cell where you want the final text to appear.
  2. Type “=CONCATENATE(“ in the formula bar.
  3. Press and hold Ctrl and then click on each cell you want to concatenate.
  4. Release the Ctrl button once you click the final cell and then type the closing parenthesis in the formula bar and press Enter.
CONCATENATE Creating one text string from several cells

As you can see, the cells are now combined. However, there is one problem, it's missing some spaces. Since you’ll probably be dealing with a large number of cells, you don’t want to do each one individually.

CONCATENATE - combining cells need space

You could fix it using either of the formulas below using CONCATENATE or the “&” Ampersand operator.

=CONCATENATE(A1, ", ",B1,C1,D1)

=(A1 & " " & B1,C1,D1)

Both of these functions separate the different arguments with spaces and/or commas into a Text String that you can now use as you like.

Here is how your Tweet will look.

@twitteruser0  Saw the tweet you shared about subject and thought you'd enjoy this too,

Example #2 Combining 2 Columns into One

Let’s say you want to combine the first name and last name into one column.

  1. Select the cell where you want the combined name to appear.
  2. Type “=CONCATENATE(“ in the formula bar.
  3. Press and hold Ctrl and then click on each cell you want to concatenate.
  4. Release the Ctrl button once you’ve clicked the first name and last name and then type the closing parenthesis in the formula bar and press Enter.
CONCATENATE- combining 2 columns into 1

Here are some variations:

=A2 & B2  Results: No spacing in name

CONCATENATE combining text strings

=A3 & " " & B3  Results: Spacing in name

CONCATENATE combining cells with spacing

=B4 & ", " & A4  Results: Last Name with comma, then 1st name

CONCATENATE - combining cells with a comma

=CONCAT(A5 & " " & B5)  Results:Using CONCAT


Example #3: Reverse the CONCATENATE function

Now that you know how to combine cells, let’s see how to break them up or rather reverse the process.

First, copy the "Full Name" column info into the "First Name" column for best results. This is the column you are actually splitting.

  1. Now, select the cells you want split. You are able to select an entire column at once.
  2. In the “Data Tools” section on the Ribbon, click “Text to Columns.”
  3. Select “Delimited” and then click NEXT.
  4. Choose “Space” as the Delimiter and then click NEXT.
  5. In the next screen, click Finish.
CONCATENATE split into columns using Delimited
CONCATENATE split into columns using space

This is your result, you have now separated 1 column into 2.

Example #4: Adding Text to the Function

In this example, you want to show how much commission you earned on an affiliate sale. You’re joining the sale amount and the commission to produce the actual dollar amount. Here’s the formula:

="Comission is: "& TEXT(20%*A2, "$#,##0.00")  

Using the TEXT function in the CONCATENATE function, you’re able to add text along with the $.

The #,## is used when you need to add commas to large numbers. The “0” is used to display the nearest integer.

0.0 is used for one decimal place. 0.00 is used for two decimal places and so on.

Example #5: Using Line Breaks

At some point, you may find it necessary to combine columns and merge them with line breaks. For example, you could be merging mail addresses from data in different columns. The issue is that you can’t type a line break into the function, but you can use the CHAR function. This function passes the ASCII code to the CONCAT formula.

CONCATENATE with line breaks

This is the formula:

=CONCAT(A2&CHAR(10)&B2&CHAR(10)&C2," ",D2)

CONCATENATE using line breaks

Once the formula is inserted, you can copy it to the other cells below it.

CONCATENATE using line breaks
CONCATENATE using wrapped text

Note: For the line breaks to appear properly, you must have the “Wrap Text” option enabled for the column where the data will appear. Press CTR + 1, select “Alignment” and then click “Wrap Text” and then click OK.

Now that you have options for using Microsoft Excel in organizing your data, how will you use it to manage your work flow?

You might also like

June 17, 2020

December 27, 2020

December 11, 2020

May 28, 2020

November 10, 2020

September 1, 2020