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, how to concatenate cells in excel, excel concatenate cells with comma, concatenate excel cells, concatenation in excel, concatenate excel mac, concatenate cells excel
I’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.
“Text to columns” is another shortcut about which I want to write about. These shortcuts do save us!
🙂
You 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)
Thank you Andy! There’s always something else that makes these formulas easier to use.
Cool 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)
Thanks 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.
There’s an even easier way to concatenate in Excel. ‘=a1&” the bean, “&b2’
I’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
Hi 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
I 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
You just tell it to replace the spaces with nothing using this formula where a1 is the cell with the original number.
=SUBSTITUTE(a1,” “,””)
~Grantastic!
I 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?
Hi! 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
Figured 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
Alan,
Thank you bud! You definitely helped me a lot here. I was about to pull my hair out. Thanks a mundo!!!!