Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've got the basic formulas down on how to break out city/state/zip into separate columns. The problem that I'm having now is that when the city has mulitiple names such as East Boston or S. Dennis, the basic formulas only pick up East and S. How can I expand the formulas to account for multiple city names?
    Attached Files Attached Files
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Enter the following formula as an array formula (i.e. confirm with Ctrl+Shift+Enter) in B2 (or C2):

    =LEFT(A2,MAX((MID(A2,ROW($1:$30),1)=" ")*ROW($1:$30))-4)

    and fill down as far as needed. If you want to get rid of the trailing period or comma after the name:

    =SUBSTITUTE(SUBSTITUTE(LEFT(A2,MAX((MID(A2,ROW($1: $30),1)=" ")*ROW($1:$30))-4),",",""),".","")

    again as an array formula.

    The 30 in ROW($1:$30) must be at least the length of the longest expected entry in column A. So if you have to accomodate place names like Llanfairpwllgwyngyllgogerychwyrndrobwllllantysilio gogogoch, you'd need to increase both occurrences of 30 to, say, 75.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Like a charm again Hans!
    thanks
    christine

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    So, what I didn't take into consideration when looking for an adjusted formula based on the city, was that it was going to affect all subsequent formulas for breaking out state and zip. I've tried to go through it on my own, but alas...nothing! I am assuming that I'll have to build arrays for state and zip also, but every one I try, bombs out. What am I missing????
    thanks
    christine

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Since you're going to need the position of the last space for city, state and zip, I'd calculate it in an auxiliary column; this is more efficient than repeating the calculation in each formula. You can hide the auxiliary column if you wish.

    So for example in E2 as array formula:
    =MAX((MID(A2,ROW($1:$30),1)=" ")*ROW($1:$30))

    The other formulas can be "normal" formulas. The one for city becomes:
    =SUBSTITUTE(SUBSTITUTE(LEFT(A2,E2-4),",",""),".","")

    For state:
    =MID(A2,E2-2,2)

    And for zip code:
    =MID(A2,E2+1,30)

    These formulas can be filled down. See the attached version.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •