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
Discussion