# Thread: City/State break with multiple names in city

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

2. 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. Like a charm again Hans!

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

5. 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.

#### Posting Permissions

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