Cleaning up spreadsheets might not be in your job description, but it’s an all too common task for me. I recently encountered a problem and needed to quickly get something out before the close of business on Friday. Thanks to my old friend Louis Alleva, he instructed me on the use Excel’s handy “concatenate function” designed to tidy up databases by joining character strings in destination cells.

I frequently find myself 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.

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).

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.

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.

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:

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

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.

The “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.

Once again I would like to thank Louis Alleva for his timely lesson on how to use this function. If you need any Accounting Solutions for your business here is a link to his new website.

http://acctbooks.org

For a summation of formulas/functions available in Excel, Visit this link for a list by category.

2008 sucked in many ways, and definitely left us all with bad memories, lost revenue, and a complete sense of frustration.
But in spite of all that baggage it’s 2009… a new year, a fresh start, another chance to get it right.

Maybe… just maybe… all we need is a change of attitude. Here’s an idea!

This dude above looks kind of happy, maybe he’s even discovered the secret!

Of course your “smileage” may vary, depending on your spouse/boss/kid/etc.