I have a list of about 8000 addresses. Unfortunately, the address field included the street, city, state and zip. The state and zip were relatively easy to extract since they were set character lengths (zipcode was 5 character and state was 2). Now what I'm left with is a string that includes the address and city.

Lastly, to extract the city, I took the remaining address data and ran a "text-to-columns". As a result, I was able to extract most of the cities, but not all of them. And now this is my dilemma.

Unfortunately, there is no space between the "last character of the address" and the "city" for almost 4000 of the records, therefore I have character strings similar to the following:

1234 Your Street Ste 104Houston
5678 My Street Building 2San Diego
91011 Our Street NWNew York

With 4000 records, it would be quite a manual exercise to extract the cities from the addresses. Does anyone have any idea of how to extract the cities from these character strings?

ONE IDEA: Many of the cities have numerical characters next to them as in the first two examples above. Is there a way to search a text string to find the rightside characters UP TO a numerical character. This would at least get me much further along. Any ideas?