PDA

View Full Version : Extract STATE from address string



suriyahi
10-02-2008, 05:04 PM
Hi Guys,

Need to extract STATEs from addresses which is in a single coloumn. How can I extract just the STATES to a separate States. For example: I am attaching a ss here. Thank you for your help here.

mikerickson
10-02-2008, 05:46 PM
Since your example has a double space before each state abbreveiation, this formula will work, without gyrations to deal with irregular City Names or postal codes.

=TRIM(MID(A2,FIND(" ",A2&" "),4))

(Copy pasting this formula from the website will not work, the BB software removes the second space between each of the quote marks in the formula. )

suriyahi
10-02-2008, 06:54 PM
Thank you for your reply.
This formula works for Cities with one word name, but cities with two words or multiple words (for example, New York, North Olmsted,Del Ray Beach, etc.), this formula does not work. Do you have any other solutions? Thank you!:banghead:

suriyahi
10-02-2008, 08:35 PM
Nevermind Mike....it worked just as you suggested. Case closed!! Thank you very much...and good night!!