Dirty Data Again

It happened again.  I was working on a project for which I needed a quick list of all of the 50 states and their abbreviations.  I probably had it somewhere— I’m sure I’ve needed this information before—but I was too lazy to look for it.  If I was in 7^th grade, I could probably have just typed them in order from memory.  But I’m not and I can’t.  So I did what any half-way smart person would do.  I googled “State Abbreviations”.  The result returned a lot of sites, as you’d expect.  I decided on using the one for our good old United States Postal Service: http://www.usps.com/ncsc/lookups/abbr_state.txt.

Just copy and paste into Excel and I’m good to go, right?  Wrong.  As is often the case, the data did not come wrapped in a nice little bow ready to use.  It is what I call “Dirty Data”.  Luckily, there is ALWAYS a way to easily— and usually quickly— clean it up.

When you copy and paste the data into Excel, you find that the name of the state and its abbreviation fall into the same cell.  And to make things worse, there is no real uniformity to it.  If they all had the same number of spaces between the name and abbreviation that might be helpful.  If there was a comma or other common character, I could work with that, too.  But I wasn’t so lucky.

But here’s how I cleaned it up.

First, I removed all the extra spaces— any space other than single spaces between words— using the Trim() function. This solved part of the problem.

Normally I’d then find the space in the cell, take the left part as the name of the state and the right as the abbreviation. For “California CA”, “Nevada NV”, and “Mississippi MS”; that would work. But how about “New Mexico NM” or “New York NY”? That won’t work.

So, like I do a lot, I did a little research online and found some possible solutions. Then, so I fully understood how to accomplish the task (in case I had a similar problem in the future) I worked my way into the problem in small pieces:

1. First, I need to figure out if it’s a state with one or two spaces. I need to count how many spaces are in the string (the trimmed string). I do this by first removing all of the spaces from the string [substitute(string,” “,””)] then taking the length of that new string without spaces [len(substitute(string,” “,””))].
2. I then subtract the length of that new string without spaces from the length of the original string ([len(string)]
3. The result of this subtraction gives me the number of spaces in the string— state and state abbreviation; this total also equals the last occurrence of a space in the string.

Here’s where it gets a little tricky…

1. Now that we know where the last space is, we replace that with any non alpha character other than a space. We do this to differentiate it from the other space if there is one. For Example, if we used the dollar sign as our charcter,”New York NY” would become “New York$NY”. We do this by using the Substitute() function again. But this time we take advantage of the optional fourth parameter—instance number. After we know how many spaces there are, we replace the last one with a dollar sign ($):SUBSTITUTE(String,” “,”$”,LEN(String)-LEN(SUBSTITUTE(String,” “,””)))
2. Now, if we just find where the dollar sign ($) is, we can determine the space that position that separates the full name from the state abbreviation: FIND(“$”,SUBSTITUTE(String,” “,”$”,LEN(String)-LEN(SUBSTITUTE(String,” “,””)))) Let’s call this formula DividePosition for simplicity.
3. And once we know where the line of separation is, we can take everything to the left of it, [left(string,DividePosition-1)], as the full name and everything to the right, [right(string, len(string)-DividePosition), as the abbreviation.
4. Once we’ve done all of the dirty work, we can copy and paste values.
5. ..then roll our sleeves back down J

Don’t worry, I’ve included a spreadsheet (Converting_Dirty_Data_StateAbbrev) to help you understand. And as always, feel free to drop me a line if you have questions.