I have column of addressed in this format:

120 Lemon Street Columbus OH 92738 (Basketball Courts)

And I need to split it up into: street address (120 Lemon Street), city (Columbus), state (OH), zip code (92738) and description ((Basketball Courts))

Is there a way I can do this? All of these are in the same state, so that is not a problem. They are in different cities/towns and have different zip codes.

If you only know how to do part of it, still let me know. Any help is appreciated, thanks!

  • 1
    Well this is a little hard to do unless you have some things set.. like is your street name always two words with one space inbetween (Lemon Street)? Is your city always one word? Basically you need to find a pattern or insert a delimiter into your entry that you can separate by. – Eric F Oct 27 '14 at 17:01
  • What have you already tried? Is VBA OK? – Mr. Mascaro Oct 27 '14 at 17:02
  • @EricF They aren't the same number of words for the street or city which is why I'm having difficulty. One thing I can think of is check whenever there are words like "Avenue, Street, Lane" etc and trim everything after. But I'm not sure if that is a good method or not – user1282637 Oct 27 '14 at 17:07
  • Even with VBA you would need a pattern to go off of or a table you are referencing. – Eric F Oct 27 '14 at 17:08
  • 2
    You're asking for a lot here. Splitting undelimited text like this is going to come down to formulating rules based on regularities in your specific data set. In my experience, this is a lot of trial and error -- trying a general rule, finding exceptions to that rule, and then incorporating another level to the rule to catch the exceptions, and so on. Without narrowing the problem, it's going to be hard for anyone without access to your specific data to help. – Excellll Oct 27 '14 at 17:14

User1282637, I have set up an example to show you below. I have only made it to be a starting point so you can finish with your own complete answer.

First, I set up two lists. One was all of the state abbreviations and the other being all the accepted street suffexes which I found here:


enter image description here

I used the formula that I listed simply to convert the list to start with a capital letter and then the rest be lower case, like how you had yours written.

Next it is simply looking up what applies..

enter image description here

This part could be done many ways but I decided on this way for an example. It will simply list a number in the corresponding row that finds a match for the suffix used.

I separate the (Basketball Courts) part by using the parenthesis:

enter image description here

I display what is remaining because in your case "Courts" from "Basketball Courts" is also a street suffix:

enter image description here

Next I need to find how long the string is that is the street suffix so I use the following:

enter image description here

and this...

enter image description here

and lastly this allows me to display just the street:

enter image description here

Now I haven't done the city and state but following this idea, you can achieve this. Also if you want a more clean option then definitely look into learning about VBA more. I do hope this at least teaches you some ideas of how to go about this.

| improve this answer | |

User1282637 asks if there is a way to accomplish this task and requests any help in doing that. The issue is not Excel mechanics as much as how to deal with the ambiguity in the data. Parsing the ZIP Code and description are straightforward (unless you have a mix of 5 digit and 9 digit ZIPs). The difficult problem is in separating the street from the city, so I'll focus on that. This is not intended as a step-by-step how-to-do it with Excel formulas. It is just sharing some insight into the problem and describing an approach for getting to a result for the hard part.

The problem is that there are no delimiters between the different fields. That is not an issue for peeling off the description or ZIP because those are easily identifiable. The problem is determining where the Street ends and City begins. Consider these variations in the Street portion (far from an exhaustive list):

120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3

The number of "words" in the street can vary from as low as 1 or 2, to as many as 7 or 8, so that isn't useful for parsing. The street "type" also isn't especially useful. There are on the order of 50-100 words used just for the street "type" (street, avenue, boulevard, way, turnpike, lane, court, circle, terrace, etc.). Combine that with the use of abbreviations for the street type, both proper and incorrect, and the list is in the hundreds. Plus, this designation isn't always the last word in the street field. The street is the hardest portion to identify so the logical approach is to identify the rest and then the remainder is the street.

The city can be several words. Washington Court House, OH is three words. Then consider situations like St Marys, OH. Is the "St" a part of the city name or a street type designation; which field does it go in? Or South Euclid, OH--is "South" part of the city name or a directional that is part of the street address? City has its problems but there is a way to deal with them.

Even using the ZIP code to identify the city has problems. There isn't always a 1:1 match between city name and ZIP.

The most practical way to attack the problem is to use "dictionaries": a list of cities, and a ZIP Code directory. These are the most unambiguous portion of the address. These can be found online or from the Postal Service. To do comparisons, you may need to clean up either your data or the listings. They will need the same capitalization style and any extra spaces in your data will prevent an exact match.

If either your data or the listing uses abbreviations, you will need to deal with that. Either translate the unabbreviated one to the standard abbreviations or do a secondary match against an abbreviations dictionary (also available online or from the Postal Service), when those differences are found.

The ZIP can be readily parsed, and that is a good place to start. Do a ZIP Code lookup against the ZIP Code directory. If the result exactly matches a string of words immediately preceding the ZIP, that identifies what portion of the record is the city field.

If there is not an exact or unambiguous match, move to a comparison of city names. Iterate through the city names list. For each name, determine the number of words it contains and compare it to that number of words immediately preceding the ZIP.

If you get a match through either process, everything remaining to the left of the city is the street address.

This type of application is much easier to do with a database application than with a spreadsheet. Either way, you can see that trying to do this in an automated way is not a simple task. You can't do it with a few spreadsheet formulas.

No matter how rigorous your programming is, you are likely to still have records you need to parse by hand, and parsing errors you need to correct by hand. You don't indicate how many records you have. It may be less work to simply do it manually.

If the number is large and I had to do it, I would cull the list. Match the records that are easy, like unambiguous ZIP matches. Then let the quantity of data govern how far you go programming automated matches.

For the records that are left, assuming you have already peeled off the ZIP and description, here is a way to speed up the manual process. Look at the record and visually identify the number of "words" in the city, which is a fast mental task. Enter that in a prescribed cell and use a formula to split the street from the city based on the number of space breaks (separate at the Nth space where N = total spaces + 1 - number of words in the city name).

| improve this answer | |

Try this out. This simple approach should work really well if you can live with having the Address and City in the same cell. I have a formula to extract the city if if's just one word, but it gets much more complex if with multiple-word cities (e.g., New York).

Formulas... Address & City: =LEFT(A2,FIND("OH",A2)-1) State: =MID(A2,FIND("OH",A2),2) - you mentioned everything is OH, so I kept it simple Zip: =MID(A2,FIND("OH",A2)+3,5) Description: =TRIM(MID(A2,FIND("OH",A2)+8,30))


enter image description here

| improve this answer | |
  • 1
    You should post the actual formulas here too. I know personally I don't like going to external links that people post. – Eric F Oct 27 '14 at 17:46


Sub SplitAddress()
    Dim Addr As String
    Dim l As Integer
    Dim Desc As String
    Dim Zip As String
    Dim State As String
    Dim City As String
    Addr = Selection
    l = InStrRev(Addr, "(")
    Desc = Right(Addr, Len(Addr) - l + 1)
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    l = InStrRev(Addr, " ")
    Zip = Right(Addr, Len(Addr) - l)
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    l = InStrRev(Addr, " ")
    State = Right(Addr, Len(Addr) - l)
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    l = InStrRev(Addr, " ")
    City = Right(Addr, Len(Addr) - l)
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    Selection.Offset(0, 1) = Addr
    Selection.Offset(0, 2) = City
    Selection.Offset(0, 3) = State
    Selection.Range("B11").Offset(0, 4) = Zip
    Selection.Range("B11").Offset(0, 5) = Desc
End Sub

Explanation: Press Alt+F11 and paste the above code into the widndow that appears. Then select a cell that contains the address and return to the window where you pasted the code and press F5.

If that works we can work on making it more specific to your specific situation.

| improve this answer | |
  • @Raystafarian, I think you misread the question. The OP says that the data is always in the supplied format. The format in question is blanks delimited. – Mr. Mascaro Oct 27 '14 at 17:34
  • 1
    What about two-word cities? Three-word cities? – Excellll Oct 27 '14 at 17:51
  • @Excellll, No solution for undelmited data will be perfect, but this will be closer than the answer you've posted. – Mr. Mascaro Oct 27 '14 at 17:53
  • 1
    well it can either be delimited or referencing a table like my answer did. I guess it just depends on how the OP wants to handle it. I responded using formulas since he didn't seem to understand what VBA is, yet alone write in it. – Eric F Oct 27 '14 at 18:05
  • 2
    @jbarker2160 I didn't post an answer. – Excellll Oct 27 '14 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.