I Concatenate, Can You?

Concatenate is defined as combining two or more strings together to form a single one.  In Excel, it can be used for the same reason.  Here’s an example.

Let’s say you have a list of names and addresses and you want to make mailing labels from it:

You want a way to take the First and Last name and make them look like “Ted Jones”.  The concatenate formula will group several strings of text together.  The format is =Concatenate(text1, text2, text3,…..).  If you enter the formula as =Concatenate(B1,B2) you will get:

Note the formula in the formula bar.  The results are close, but you probably want a space between the first and last name.  Here are two ways to do that.  First, you can include into the string in your formula a space.  I’ve done that here by entering into cell A1 a space, but I need to precede it with an apostrophe ( ‘ ) which indicates that whatever follows the apostrophe is text.:

In the image above I’ve shown in Column F the formulas that are located in Column D.  The second way to accomplish this is using the formula that is shown in cell F8:  =CONCATENATE(B2&” “&C2).  Saying this formula in words would go something like this:  string the text in cell B2 AND a space AND the text in cell C2). 

Note also the concatenation of the City, State, and Zip.  In that string, I’ve also included cell A2 which is a comma and a space in order to format the address appropriately:

Note that I didn’t need to start with an apostrophe since a comma is already considered text.  

Can you think of other ways to use Concatenate?  Try it out.  It’s a nice tool!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply