Video Tip of the Week – Separating City, State Zip in Excel

In today’s video, I show you how we use Excel to separate the City, State, and Zip Code and if you want the excel file I mention in the video, be sure to enter your name and email below and I will send you the excel file for free.

Join the newsletter

 

Subscribe to get our latest content by email.

 

Transcript

Hey, it’s Scott Todd and in today’s Excel video tip of the week, I wanted to share with you the one little tour, the way that we actually separate city, state and zip code when a county gives that to us, you know, you can’t get a mailing list from a county and have it perfectly delivered to you, sometimes you need to kind of manipulate the data and sometimes they give you the city, state and zip code altogether and you stop to go back, and you know, kind of manipulate it into the separate columns. Stop, let me show you, let me show you what I’m talking about.

Okay, so here we are in Excel, you’ll see that we have our list here, there’s a one column that says city, state zip code and I need it in the three columns, so you know you could do the normal you know, manual labor of cutting, pasting, trim all that stuff. We’re not going to do that, we’re gonna work smart, what I’m gonna do is I’m gonna highlight these three columns right here because they need to insert three columns, so what I’m gonna do is, I’m gonna highlight them, I’m using the Mac so I’m gonna hit Ctrl Shift + and that’s gonna add my three columns, I’m gonna go ahead and give them titles here, city, state and zip code. There we go, now what I’m gonna do is and I’ve made this so easy for you, I’m gonna give you basically this Excel template that you can use. I’m gonna tell you at the end how to get it it’s free. I’m gonna tell you how to get it so that you can use this as you need to but check this out, I’m just gonna copy this down,

I’m gonna copy the city, state, zip code over here, I have put into this Excel spreadsheet this tab over here, and in this tab, you’ll see that, you’ll notice that in city, state, zip code I already have the formulas that you need right there, so what I’ll do is I’m gonna come over here, I’m just gonna paste that — “voila!” city, state, zip code is all cleaned! It’s separated, but I still have some cleanup to do to it. So, let me show you what I’m gonna do now, what I’m gonna do, I just gotta take this column here, I’m gonna take these three columns, I’m going to copy them, I’m gonna come back over here, and I’m just gonna paste them right back over here into my my file that I’m working with, what I should have done is, I should have done spaced value. Let me show you what I did, I’m gonna clean this up, let me undo it, what I’m gonna do is I’m gonna come back over here, I’m going to right-click, do paste special values, and when I do that, you’ll notice that the formula here is no longer the formula, is no longer here, so I can delete this combined file on my master worksheet and now I still need to do some cleanup to this.

What I need to do first, I need to first thing, I need to do is, I need to make sure that all of these zip codes are 0, you know have the proper digits for not four digits– five digits right, and I’ve shown previously how to do that; but I’ll just do it again real fast, I’m gonna come over here, right click Format Cells, come to Special, there’s a couple ways you can do it, special zip code, or you can do it manually; let me come over here, I’m gonna do this again, it’s custom one, two, three, four, five, boom – it’s not working, let me investigate; okay, so the reason this is not working for me real fast is this it’s telling me right here – like you see this little exclamation point when I click that? – it says this number is formatted as a text, so what I want to do is, I want to convert this to a number, now that I’ve converted to a number, I can come over here and do format cells. I’m just going to do special zip code, boom! – I have that.

I now have my five digit zip codes in there perfect. The last thing that I want to do though, is I want to clean this up because I don’t want the commas at the end here, so what I’m gonna do here is, I’m gonna come over here, I’m just gonna do a find and in Excel module Find and Replace, so I’m just gonna do replace right here, and what I must tell it to do is, when it sees a comma in that column, just replace it with with a blank, so I don’t have to put anything there, when I see a comma replace it with a blank and replace all. It’s found 22 instances, closed; so, now what I have is, I have a properly formatted list with city, state zip code. City looks clean, state looks good, my zip codes are all five digits. I am ready to rock and roll! Very quick, very simple.

I hope that you found this tip, you know very helpful. I want to give you the Excel file, so what I want you to do is, I want you to go over to ScottTodd.net/excelcity, Excel city altogether, and I’ll put the link right below me right here. Go there, you can download the Excel file with the formulas, so you don’t have to recreate the formulas. It’s made easy for you, it’s like having your own easy button and if you found this to be valuable please, leave your comments. Wherever you’re watching this YouTube Facebook or on ScottTodd.net please, make sure that you leave comments and I will see you again soon, have a great week.

2 replies
  1. Geoffrey Pierce
    Geoffrey Pierce says:

    Scott, in my pre-real estate j.o.b. I did address parsing all the time in Excel; up to tens of thousands of rows sometimes with some hairy formula work. Never once did I think of that formatting trick for the zips. We had macros in Access doing it, but that’s a forehead smacker. Excited to see my parsing skills will once again be earning me money!

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *