How to parse address text string?

[caliray]

I need to parse an address text string to separate the city into a separate column. Two example addresses are:
Cell A1 contains 2425 West Loop South Houston and cell A2 contains 2426 West Loop Houston.
This formula =RIGHT($A2;LEN($A2)-FIND(" “;$A2)) in cell B2 returns West Loop South Houston and =RIGHT($A3;LEN($A3)-FIND(” ";$A3)) in cell B3 returns West Loop Houston. Both of these results return part of the street address with the city, but I need to return only the city. The city can be two words as in “South Houston” or a single word “Houston”. The length of the city name is unknown.

Thanks for help with this.

[JohnSUN]

There’s no commas, dots, semicolons, etc.? Space only? IMHO, this is impossible, your task in this form has no solution, sorry

[caliray]

I think you are correct. One space looks like all of the others and with an undefined length for city, I don’t see how this can be done in Calc either, but it was worth asking.

[JohnSUN]

it was worth asking Yes, you are right - funny questions can give ridiculous solutions

[JohnSUN]

If you are willing to maintain a list of cities, then possible this solution will satisfy you

Town from address.ods
Main trick is a formula in a named range foundTown. Just set cursor to cell Addresses.B2, press Ctrl+F3, select (or create) foundTown and change field Range to

IF(SUMPRODUCT(MAX(IF(RIGHT(A2;LEN(Town))=Town;ROW(Town);0)))<2;"<insert this town to dictionary>";  INDEX(Town;SUMPRODUCT(MAX(IF(RIGHT(A2;LEN(Town))=Town;ROW(Town);0)));0))

Result must be something as

[caliray]

This looks like one way to approach the problem. Keeping a list of cities would be well worth achieving the desired result. Thank you.

[JohnSUN]

Please avoid coincidence of names named ranges and column headings. Because of the identical words Town I got a nasty bug. The error disappeared when I change the column header to City. Unfortunately, this solution doesn’t work in the AOO