• City/State break with multiple names in city

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » City/State break with multiple names in city

    Author
    Topic
    #460529

    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?

    Viewing 2 reply threads
    Author
    Replies
    • #1164940

      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 Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch, you’d need to increase both occurrences of 30 to, say, 75.

    • #1164956

      Like a charm again Hans!

    • #1165012

      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????

      • #1165018

        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.

    Viewing 2 reply threads
    Reply To: City/State break with multiple names in city

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: