Join character strings with Excel's concatenate function

by Beth Blakely | Aug 08, 2002 7:00:00 AM

Takeaway: Cleaning up spreadsheets might not be in your job description, but it's an all too common task for some consultants. Use Excel's handy concatenate function to tidy up databases by joining character strings in destination cells.

Consultants frequently find themselves manipulating simple Microsoft Excel databases. Often, the databases have been pulled from varied sources and need some cleaning up before they can be of any use. Many times that cleanup calls for tedious manual manipulation of the data.

Excel’s concatenate function may help in cases in which you need to join character strings from several cells together in another destination cell. For example, you might have a database of U.S. addresses with the first five digits of the ZIP code in one field and the other four digits in another. If you need those digits strung together with a dash between them in one field, the concatenate function can make that a simple task. These step-by-step instructions will show you how.

Step one: Create or choose a destination cell
For the purpose of this demonstration, I created a database with the ZIP code problem described above. I’ll use the concatenate function to join the two fields. The first step in joining the strings is to create or choose a destination cell for the joined character string. I inserted a column after the two ZIP fields and named it ZIP+4, as shown in Figure A.

Figure A

Step two: Type the concatenate formula
The destination cell for the first joined character string will be G2, so the next step is to type the formula =CONCATENATE(E2,"-",F2) into the formula bar, as illustrated in Figure B. Everything after the function should be enclosed in parentheses, and commas should be used to separate the cells. Any text to be included should be enclosed in quotation marks, like the dash (-) in our example.

You may also use the ampersand (&) symbol instead of the word “concatenate” to accomplish this task. If you chose to use the ampersand, the formula would be =E2&"-"&F2. Notice that the commas and parentheses are no longer necessary, and that ampersands indicate each joint where cells and/or text will meet.

After you’ve entered the formula, press the [Enter] key. You should see the joined string in the destination cell. In our example, the destination cell G4 now contains the first five digits followed by a dash and the last four digits (see Figure B).

Figure B

Step three: Use the fill handle
If you need to join data in multiple cells, you may use the fill handle to copy the formula to adjacent cells. In this example, I used the fill handle to copy the formula down the inserted row.

The fill handle is the small black square located in the lower right corner of any highlighted cell. When you point to the fill handle, the pointer arrow changes to a black cross, as shown in Figure C.

Figure C

Click, hold, and drag the fill handle down the row of cells. Excel’s relative referencing feature will ensure that the cell names are properly replaced in the copied formulas. When you release the fill handle, you should see that the destination cells now contain the joined character string.

Warning: Destination cells contain formulas only
You may now be tempted to delete the cells containing the original information you have joined in the destination cells. In this example, that would be rows E and F, as shown in Figure B. However, you must remember that the destination cells only contain formulas, which pull information from the original cells. If you delete the cells that the formula is pulling its information from, the cell will display an error message, “#REF!,” as shown in Figure D.

Figure D

To avoid this error, you’ll need to paste the formula’s results as actual values into your destination cells. To do so, follow these steps:

Highlight all of the cells with the concatenate formulas.
From the Edit menu, select Copy.
From the Edit menu, select Paste Special.
When the Paste Special dialog box appears, select the Values radio button in the top section, as shown in Figure E.
Click OK.

Figure E

Your destination cells will now contain the actual values, or character strings. You can see this by clicking on any of the cells and looking in the formula bar. Instead of a formula, you’ll see the actual character string.

Concatenate function has many uses
The concatenate function can join up to 30 single-cell references containing text or numbers. You can also use it in conjunction with Excel’s Text function when joining text and values, such as numbers or dates. For more information about this feature, search Excel’s help files or visit the Microsoft Knowledge Base.

What’s your favorite function
What’s your favorite Excel function? Do you use it in a creative or unorthodox way? Send us an e-mail and tell us about it.

17 comment(s)