extract fields

Hi please help me to extract city from address when no seperators where used
Eg:address is -'800 Town And Country Blvd Houston, TX'.i have to extract city from this.please help me to do this

Nick's picture

if there is always a comma

if there is always a comma after the city, you can look for the comma using the FIND function, then work back

in A1:
800 Town And Country Blvd Houston, TX
in B1: =FIND(",",A1,1)
in C1: =FIND(" ",A1,B1-10) (Adjust the 10 to extract the cities)
in D1: =MID(A1,C1,B1-C1)

=> Houston