How to Clean Data with Text Functions in Excel

By Stephen L. Nelson, E. C. Nelson

One of the common problems with data that you import into Excel is that your text labels aren’t quite right. For example, you might find yourself with the city, state, and ZIP code information that’s part of an address stored in a single cell rather than in three separate cells.

Or, you might find that same information stored in three separate cells when you want the data stored in a single cell. You might also find that pieces of information that you want stored as labels instead are stored as values and vice versa.

What’s the big deal?

Just to give you a quick idea of what this means, take a look at these worksheets. Okay, this is fake data, sure. But the examples show a common situation. The list information you see here uses unnecessarily lengthy product names, goofs up some customer names by appending store numbers to customer names, and then puts all of the city and state information into one field. Yuk.

image0.jpg

Check out how this information is rearranged so that it’s much more easily sorted and filtered. For example, the PRODUCT2 field abbreviates the product name by changing Big Bob’s Guide to to just BBgt.

The store names are essentially edited down to just the first word in the store name — an easy change that enables you to see sales for Bean’s Tackle, Mac’s Shack, and Steve’s Charters. The ADDRESS information is split into two fields: CITY and STATE.

image1.jpg

Here’s one other important point: The rearrangement makes it possible to cross-tabulate the data using a pivot table (something.

The answer to some of your problems

All of this editing is performed using text functions.

Excel provides two dozen text functions that enable you to manipulate text strings in ways to easily rearrange and manipulate the data that you import into an Excel workbook.

If you’ve just read the word function and you’re scratching your head, you might want to review the contents of the Appendix.

To get descriptions of text functions, click the down arrow button next to the AutoSum function on the Home tab and choose More Functions from the drop-down list Excel displays.

When Excel displays the Insert Function dialog box, select the Text entry from the Or Select A Category box, and then scroll through the list of text functions that Excel displays in the Select a Function box until you see the function that you have a question for.

In Excel 2007 or Excel 2010, you choose the Home Tab’s choose Insert→Function to display the Insert Function dialog box.