Excel is one of the programs that made me want to throw things at Bill Gates but I found ways to make it more usable lest I end up in anger management therapy. Especially if you’re one of us assemblers of lists (guest lists, media lists, potential client lists) you should know for your own good how to combine and separate cell contents.

In this Excel how-to I’m going to teach you how to combine cell contents, which will save you loads of time or at least keep you from traumatizing your interns who enter data manually. I’m using a Mac but the process is the same for PC’s. I hope this article is clear, useful and not too boring. If you have any questions just send me a note!

If you’re a newbie, please know before reading on:

- Read “A1:D1” as “cells A1 through D1”
- The Control button on the PC is the button on the Mac
- Basic shortcuts
- Copy: Control or +C
- Cut: Control or +X
- Paste: Control or +V

**How to Concatenate in Excel**

In Excel, to concatenate means to combine cell contents.

Use the formula below (do not use any spaces) then hit enter to process it:

**=concatenate(A1,B1)**

Definition of concatenate (dictionary.com): to connect or link in a series or chain.

#### Examples

Let’s start with a 2-row database with some punctuation. We are going to merge the first 4 cells (A1:D1) into our fifth cells (E1 and E2) as shown below:

**Example 1**: for “Betty Boop, cartoon” (cell E1) the formula is =concatenate(A1,B1,C1,D1)

To get the space between the contents of two cells, add the space in the cell to be concatenated and not in the concatenation formula: to get the space between the comma and “cartoon” add a space after the comma in the comma cell (C1) or before “cartoon” in the “cartoon” cell (D1). Note that adding spaces in both C1 and D1 will give you 2 spaces in your concatenated result.

**Example 2**: for “Catherine Zeta-Jones” (cell E2) use formula =concatenate(A2,B2,C2,D2)

Note: if Excel identifies the dash as a minus sign then precede the dash by an apostrophe in the dash cell (C2), not in the formula.

You may change the content of the cells you concatenated after you process the formula and the formula will update the results automatically. For example, if you want to change “Catherine” to “Katie” just go in cell A2, delete “Catherine,” type in “Katie” and E2 will automatically change to “Katie Zeta-Jones.”

**Copying and Pasting the Formula**

After you write your formula and hit enter you will see the concatenation result automatically (cells E1 and E2). HOWEVER, the content of the cells is still just the formula and not what you see on your screen. Take a look at cell E1 in Image 1 above and you’ll see “Betty Boop, cartoon” but in Image 2 below I have clicked in E1 and you may see that the value of the cell is actually the concatenation formula:

Note: to get out of the formula hit Escape. Do not click into another cell as that will only add that cell to the formula (if you do this though just hit Escape).

At this point you may copy the formula to concatenate other rows of cells: click on the formula cell (E1), press Control or +C to copy the formula, select the cell where you want to paste the formula then press Control or +V to paste. In our example I entered the contents into A1:D1 manually then copied and pasted the formula in E1 into E2, which instantly showed “Catherine Zeta-Jones.”

There is no need to change the values of the formula to reflect the new cells you want to concatenate: even though the formula in E1 includes A1,B1,C1,D1, once you paste it into E2 Excel knows that you are referring to A2,B2,C2,D2. This is really useful because if you have a long list of cells to concatenate you can just paste the formula into all the destination cells and each of them will concatenate automatically.

**Concatenating More Cells or Fewer Cells**

Some of your data might be spread out in fewer cells or in more cell than shown so far. In the example below “John Wayne” is spread into just 2 cells but you can use the same formula you used to concatenate 4 cells since cells C3 and D3 are blank anyway:

To concatenate more than 4 cells you’ll need to add the additional cells to your formula manually by pasting the formula then double clicking into it and placing your cursor where you want the additional cells to go. In the screenshot below I pasted our formula in cell F4 and you can see how the formula used only the last four cells preceding the formula cell (B4, C4, D4 and E4). See Image 4:

To add cell A4 to the formula we double click in F4 and either type in “A4” or just click on cell A4. Do not forget to add the comma after “A4” in the formula in either case (Image 5):

**Moving the Formula Cells**

You may move the formula cells and keep them intact by cutting and pasting. In the example above your columns are out of line: 3 concatenated cells in column E and 1 in column F. To get all your results into column F just select and cut cells E1, E2 and E3 and paste them into F1, F2 and F3:

**Getting Rid of the Formula and Keeping the Contents**

Once you have finished concatenating you may get rid of the formulas and keep just the concatenated results:

Copy cell E1 then right-click in E1 and select “Paste Special” (Image 7):

In the dialog box that pops up, select “Values” then click “OK” (Image 8):

Your formula is all gone now and the value of the cell is actually what you see on your screen. In this case the value of cell E1 is now “Betty Boop, cartoon” (Image 9):

Note that once you paste the values you may only change the value of the cells manually. For example if you want to delete “, cartoon” from cell E1 you must double click in E1 then select and delete “, cartoon.”

Tip:Select a whole column or row by clicking on the letter or number header of the target column or row.

excel concatenate cells, excel concatenate, how to concatenate in excel, concatenate cells in excel, excel concatenate cells with comma, concatenate excel cells, concatenation in excel, concatenate excel mac, how to concatenate cells in excel, concatenate cells excel

Tonyon the 14th NovemberI’m glad to see that I’m not the only one who gets excited about things like this. Concatenate saved my butt once upon a time.

Also helpful: Data->”Text to Columns” performs the same kind of thing but in reverse. Great help for when your colleagues continually forget to put city, state, and zip into separate columns.

Ana da Silvaon the 25th November“Text to columns” is another shortcut about which I want to write about. These shortcuts do save us!

Andy Palmeron the 14th NovemberYou can avoid having a column for punctuation by using the formula:

=concatenate(A1,B1,”, “,C1)

or for the phone number example

=concatenate(A1,”-”,B1,”-”,C1)

Ana da Silvaon the 25th NovemberThank you Andy! There’s always something else that makes these formulas easier to use.

Bryanon the 17th NovemberCool tip. I use Excel quite a bit and a year or so ago I found a PODCAST put out by Mr. Excel (Bill Jelen) where he shows you a 2-minute video tip every day. I always thought it was cool. You can subscribe to it in Itunes.

(I’m not associated with him…just thought it was cool)

Ana da Silvaon the 25th NovemberThanks Bryan. I’ve always found it frustrating to resort to the Help function in Excel (or anything MS) as it rarely results in useful answers.

Lorin Riverson the 3rd DecemberThere’s an even easier way to concatenate in Excel. ‘=a1&” the bean, “&b2′

ducky1910on the 19th FebruaryI’m having a problem maybe you can help I’m using excel 2002

I’m combining two cells of numbers(changed to text)into a 3rd cell

my formula

A1&, ” “, &B2 or

CONCATENATE(A2,” “,B2)and all I see is the formula

Ana da Silvaon the 21st FebruaryHi Ducky,

In your post you don’t use the “=” (equal) before “concatenate.” You need to use the “=” for the formula to work.

If you still have a problem please just send me a message.

Cheers!

Ana

Melissa Mon the 28th MarchI am trying to concatenate this: 200 020 4000568 7231 into a long number without the spaces. But I can’t figure out how to do this. Can anyone help?

Thanks,

Melissa

Grant Harrison the 6th AprilYou just tell it to replace the spaces with nothing using this formula where a1 is the cell with the original number.

=SUBSTITUTE(a1,” “,”")

~Grantastic!

Shellyon the 26th JulyI have tried both the concatenate string and also the & string to several cells.

=K4021&”-”&F4021&”-”&BN4021&”-”&AR4021&”-”&AV4021&”-”&BN4021, some of the cells in BN and AR have #N/A in them and now the entire return string is coming up with #N/A. Any advise?

Alanon the 26th FebruaryHi! I hope you guys can help me

I am concatenating address fields (Name, Street, City, State, Zip)

As you know, Excel dislikes leading zeros (example: zip code 01125) so I have formatted that column as a zip code format.

When I concatenate all 5 columns into 1, I lose the leading zeros in the Zip column.

I then thought, well, maybe I’ll paste the values into a new column first, but i haven’t been able to do that either, the “Paste Values” option doesn’t retain the leading zero. Can anyone help me retain the leading zero?

Thanks!

Alan

Alanon the 26th FebruaryFigured it out! Here’s the formula I used:

=CONCATENATE(B2, “, “, C2, “, “, D2, ” “, (TEXT(E2,”00000″)))

The Key to saving the leading zero was this:

(TEXT(E2,”00000″))

Thanks! Hopefully this helps someone in the future

Shehzad Khanon the 20th MayAlan,

Thank you bud! You definitely helped me a lot here. I was about to pull my hair out. Thanks a mundo!!!!