Today, I’m doing a little work in excel (writing code that writes code – a story for another day). One of the things I needed to do was get a list of all of the U.S. state abbreviations. I had a list to start with, but it was formatted like this:
Now I could have gone through and written out AL, AK, etc in the next column, but Excel has some tricks to do this. The first thing I did was copy and paste the full set of data onto the spreadsheet. With all of the data selected, I went to the Data tab, and selected “Text to Columns”. This is a useful tool that will break up one column into two or more. Select Delimited, and hit next. Because we’re trying to split the state name from the abbreviation, select Other, and type a colon ( : ) in the small text box. You can now hit finish, and you will have two columns, one with the name, and one with the abbreviation.
As I’m typing this out, I realized that I could have been done here by setting the apostrophe as the delimiter. Doing this gives us 5 columns: spaces, state names, colons, state abbreviates, and commas. Simply delete the columns you don’t need, and you’re done.
Now I had a column with 6 consistent characters in it: a space, an apostrophe, the two-letter abbreviation, another apostrophe, and a comma. This is where we can use the Excel formula MID to grab the state abbreviation. Thanks to the Udemy Blog for a breakdown of LEFT, RIGHT, and MID. The formula =MID(A1, 3, 2) pulled out the abbreviation. Let’s break this down:
- The equals sign ( = ) lets Excel know that we’re writing a formula.
- MID, the name of the formula will pull out characters from another cell.
- A1 is the cell that contains the data we’re messing with ( ‘AL’,)
- 3 – We’re starting with the 3rd character
- 2 – We want 2 characters in the new cell
Press enter on the formula, and you will see just AL in cell B1. Now we can copy and paste cell B1 onto cells B2 through B50. Excel will automatically adjust the formula as for each row. All of our state abbreviations are done!
Just one more thing – we just copied B1 to B2, and instead of pasting AL, Excel adjusted and pasted the formula! We’ll want to copy off just the abbreviations as text, not formulas. So copy cells B1 through B50, and paste into cell C1. At the bottom of your paste, Excel will show a small “Paste As” menu. Click on this to open up more options and select “Paste Values”. Now we just have letters, not formulas.
To clean things up, delete columns A and B, and your data will be ready to go in the new column A.
Have a question for Today’s Tech Explained? Let us know! Our contact form can be found here: Contact Today’s Tech Explained.