Separate address elements from 1 cell in Excel

16,736

Analyze your problem!

  1. you want to split your address string at the comma
  2. you then want to split the right fragment from (1) at the first blank

ad 1): you get the position of the comma using =FIND(",", A1), and use the result in a =LEFT(...) and a =RIGHT(...) - for the latter you also need the string length (=LEN(...))

  • B1: =LEFT(A1;FIND(",";A1)-1)
  • C1: =RIGHT(A1;LEN(A1)-LEN(B1)-2)

Now comes the fun part ... in your 3rd example we mustn't split on the first comma, but on the third comma ... or as a more general rule, we always must split on the last comma .... but how do we find how many commas we have in the string, to feed its position as an additional argument into the =FIND(...) function?

Quick answer: look at Stackoverflow (exactly here) ... very clever ... subtract the length of the string with all commas removed from the original length, and then replace the last occurence of the comma by something else, because =SUBSTITUTE(...) works on occurence, whilst =FIND() only works on position. If you incorporate all this this, you will have

  • B1: =LEFT(A1;FIND("@";SUBSTITUTE(A1;",";"@"; LEN(A1)-LEN(SUBSTITUTE(A1;",";""))))-1) --> full address
  • C1: (same as above)

Here we use "@" as a neutral substitution string for the final comma as we asume that no address uses the "@"

ad 2): you apply the above (with blank instead of comma) once again to the right part. You can use the simple first version of the formulae as it's clear you want to split at the first blank

  • D1: =LEFT(C1;FIND(" ";C1)-1) --> state
  • E1: =RIGHT(C1;LEN(C1)-LEN(D1)-1) --> ZIP code
Share:
16,736
user2320271
Author by

user2320271

Updated on June 04, 2022

Comments

  • user2320271
    user2320271 over 1 year

    I have thousands of addresses in this format:

    123 Happy St. Kansas City, MO 64521

    9812 Main Street Minneapolis, MN 62154

    12 Virgina Ave, Apt 8, Dallas, TX 54334

    I want to extract the address, city, state, zip into individual cells (without using VB if possible). I've tried a couple variations of other methods posted, but I can't quite get desired results.

  • user2320271
    user2320271 over 10 years
    Is there a way to extract the city - and use logic to determine if the city has two words? (ie Kansas City, San Francisco, etc.)
  • MikeD
    MikeD over 10 years
    From your sample data I'd say no, because you seem not to have a reliable delimiter between street address and city, it's either a blank or a comma