Chapter 11. Text Functions: Letters as data

Your database of analytic customers just crashed!

Lightning smashed into your office and wiped out all your hard drives, including your customer database. No problem. Just go get the backup disks, right?

Wrong. The guy in charge of backups forgot to do them (he sends his apologies). Fortunately, you have something you might be able to use. Word is, a garbled scrap of email sent a few days ago might have salvageable information about your contacts....

Here’s the data

That email has your client list, all right, but the list isn’t looking so hot. All the data is mashed together. One of your employees loaded it and saved it to an Excel file for you....

Load this!

www.headfirstlabs.com/books/hfexcel/hfe_ch11_messy_data.xlsx

Brain Power

Look at the toolbar. What feature of Excel do you think would break this data into multiple columns?

Text to Columns uses a delimiter to split up your data

Geek Bits

CSV is a really popular file format for data. The letters stand for Comma Separated Value. For these files, commas act as the delimiter. The format is so common that when you load a CSV file, Excel automatically splits the data into columns using the comma delimiter.

Text to Columns is a great feature that lets you split your data into columns using a delimiter, which is simply a text character that signifies the breaks between the different data points. If your delimiter is, say, a period, Text to Columns will put the data to the left of the period in one column, the data to the right in another, and then it’ll delete the period.

If you have more than one type of delimiter, you might have to run Text to Columns more than once. In this case, you have a period acting as a delimiter, as well as a comma, and you could even treat those weird “s” characters as delimiters, which would make Excel throw them out.

Click Finish to skip step 3 of the Wizard, which is about number formatting.

Text to Columns doesn’t work in all cases

For starters, you need a delimiter, or at least you need the data elements to be evenly spaced. Here you have neither: the “s” characters aren’t evenly spaced, they aren’t delimiters, and there’s nothing that separates them from the next data element: the last name.

Better click Undo a couple times to start from scratch. You’re going to need some more firepower for this problem. Weren’t there formulas for dealing with text data?

Do this!

Press Undo a few times to get the data back to its original messy state, and then look up text formulas in Help files.

Excel has a suite of functions for dealing with text

Earlier you used the function VALUE() to convert text data to numbers, but VALUE() is just the beginning of Excel’s text functions. Excel has a whole suite of functions to deal with all sorts of situations in which you need to change or query text data.

You’re going to use a few of these to clean up this database, and in the future when you have text problems that Text to Columns can’t solve, you should check out the Help files for other function-based solutions.

LEFT and RIGHT are basic text extraction functions

You need to extract characters on the left side of your cells (the “s” characters) and on the right side of your cells (the phone numbers). To do this, you can use the LEFT() and RIGHT() functions. Here’s the syntax.

You’ll put a formula with this function in a new cell, and the formula will point to your original raw data and say how many characters to grab.

You need to vary the values that go into the second argument

In the RIGHT() formula you used to extract phone numbers, you told Excel to extract 12 characters, which works for all the phone numbers. But the count of “s” characters varies among the cells—from one character to five.

So when you create a LEFT() formula to extract the “s” characters, the value of your second argument somehow needs to vary among the cells.

Business is starting to suffer for lack of customer data

That data you’re working on is really important, and without it your employees are starting to have problems.

Better punch through those first and last names really quickly!

You know, because you already extracted values on both ends of your raw data, it’d be nice if you could use that information to get the name out. It’d be nice if you could use the data you’ve extracted to trim the ends off of your raw data.

Let’s use the stars and phone fields to whittle down the original. That way, breaking apart the last name and first name will be easier.

This spreadsheet is starting to get large!

The spreadsheet is getting complex, but we’re making lots of progress. Go ahead and copy/paste the two formulas you just created for the remaining rows in your spreadsheet.

You need a way to extract the last name from the data you created in column D. It seems clear that you can use a LEFT() formula, but you need to create another formula-based argument to specify the number of characters to grab.

You need a formula that will state the numerical position of the comma. When you use it as your argument, your LEFT() formula will know just how many characters to grab to return the last name.

What formula will state the numerical position of the comma?

FIND returns a number specifying the position of text

FIND() is a function that returns a number that states where a search string can be found within a piece of text. Say you were looking for the position of the text “x” in the expression “Head First Excel”.

Why would you need a function like this? Well, for starters, you could use it in conjunction with a LEFT() or RIGHT() formula to extract a number of characters that varies from formula to formula.

Let’s use FIND() to extract our Last Name field....

Can you run Text to Columns on column Messy 2? What happens when you try?

Text to Columns sees your formulas, not their results

There’s a little snag when it comes to running this operation on the data you created in the Messy 2 column:

Text to Columns does what it says it does: take text and break it into columns. But here Excel wants to treat your formulas as text. This won’t work: you need to take the formulas and render them as values.

Paste Special lets you paste with options

Paste Special is a fantastically helpful operation in Excel that lets you copy something and then—rather than paste an exact copy of the original—paste a modification of the original.

You can use Paste Special to paste the values that the formulas you’ve copied return, rather than the formulas themselves. And that is just what you need to do with your Messy 2 column data.

Looks like time’s running out...

Your data crisis is solved!

This chapter started off with a real mess: you received a pile of jumbled-up data, which was all you had left of your customer database. But with the help of Excel’s powerful text formulas, you fixed that messy data right up.

LEFT	Removes duplicate spaces and spaces on each end of text in a cell.
RIGHT	Grabs the leftmost text in a cell. You tell it how many characters you want.
FIND	Returns a value equal to two or more text cells mashed together.
TRIM	Returns a number that represents the position of a search string in a cell.
CONCATENATE	Returns text from the righthand side of the cell.

Q:	Q: Wouldn’t Text to Columns be an easier way to deal with this problem?
A:	A: Text to Columns is definitely quick and easy. You could use it in this case.
Q:	Q: So why didn’t we?
A:	A: Whether and when you use Text to Columns versus formulas is really a personal preference, and there is nothing wrong with using it here. But there is one big, fat reason to use formulas primarily.
Q:	Q: Because it’s the harder way, so it’s easier to show off?
A:	A: Not at all! You’d always want to use formulas in situations where you think you might want to go back and trace exactly how your clean data was derived from your messy data.
Q:	Q: Why would you care? As long as the clean data works correctly, aren’t you in good shape?
A:	A: If you have messy data that has a single, simple pattern to it, you probably wouldn’t have to go back and see how you derived your clean data. If every data point is separated by a delimiter, and you run a Text to Columns, you probably won’t have problems with your cleaned data not squaring with your original data.
Q:	Q: But if the original data is complicated, it’s a different story.
A:	A: Exactly. The customer data you salvaged from the email has several patterns to the messiness: the first two fields (stars and names) aren’t separated by a delimiter, the last name and the first name are separated by a comma, the first name and the phone number are separated by a period, and don’t forget the asterisks inside the phone number.
Q:	Q: The data is really messy.
A:	A: And because it’s so messy, you’ve had to do a bunch of things to fix it. In creating the big, formula-filled spreadsheet you used to clean the data, you’ve also set up an audit that you can review if your clean data doesn’t match your messy data perfectly later on.
Q:	Q: But there are still a lot of cases where I need to use Text to Columns, right?
A:	A: Totally. Cleaning messy data—which all of us have to do at one point or another—is about finding the boundary conditions between your individual data points. And those boundaries are usually delimiters of some sort. If it’s not a comma or a period, it might be spaces. So most of the field of cleaning messy data involves identifying those boundary conditions and making the software split the data using them.
Q:	Q: Which is what Text to Columns does.
A:	A: Right. And if you run it over and over, Text to Columns can usually make some pretty complicated breaks. Just remember that you sacrifice the ability to go back and tweak the formulas you used to get different results. Once you run Text to Columns on data, it deletes the original data and leaves you with new columns.
Q:	Q: I think that on the first name, which is the last data field we have, we’re safe to go with Text to Columns. By now, all the formulaic work we’ve done to break up the original mess has made what’s left pretty simple.
A:	A: Then go right ahead and use Text to Columns!