Splitting up an address into different cells

6,860

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:

http://pe.usps.com/text/pub28/28apc_002.htm

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.

Share:
6,860

Related videos on Youtube

user1282637
Author by

user1282637

Updated on September 18, 2022

Comments

  • user1282637
    user1282637 about 1 year

    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!

    • Eric F
      Eric F about 9 years
      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.
    • Mr. Mascaro
      Mr. Mascaro about 9 years
      What have you already tried? Is VBA OK?
    • user1282637
      user1282637 about 9 years
      @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
    • Eric F
      Eric F about 9 years
      Even with VBA you would need a pattern to go off of or a table you are referencing.
    • user1282637
      user1282637 about 9 years
      @jbarker2160 Sorry, I've used Excel only a few times, not sure what VBA is. Coul you elaborate?
    • Excellll
      Excellll about 9 years
      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.
  • Mr. Mascaro
    Mr. Mascaro about 9 years
    @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.
  • Eric F
    Eric F about 9 years
    You should post the actual formulas here too. I know personally I don't like going to external links that people post.
  • Excellll
    Excellll about 9 years
    What about two-word cities? Three-word cities?
  • Mr. Mascaro
    Mr. Mascaro about 9 years
    @Excellll, No solution for undelmited data will be perfect, but this will be closer than the answer you've posted.
  • Eric F
    Eric F about 9 years
    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.
  • Excellll
    Excellll about 9 years
    @jbarker2160 I didn't post an answer.