Is there a formula to extract the state from an address?

Christina09

Hi!!

Is there a formula to extract the state from the following address? The state should be after the comma and before the zip code number.

12345 Paris Rd Dallas, Texas 75240 United States Validation Status: Validated

9856 SLEEPING BEAR RD SKOKIE, Illinois 60076-1920 COOK United States Validation Status: Validated

333 E 123TH ST APT 195 NEW YORK, New York 10017-1628 NEW YORK United States Validation Status: Validated

556 Valley Ln. West Greenwich, Rhode Island 02817 United States Validation Status: Validated

Thank you for your help.

Leibel S

@Christina09

This was a fun one :)

Hope it works:

=MID(Address@row, FIND(", ", Address@row) + 2, FIND("~~~~~", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Address@row, "0", "~"), "1", "~"), "2", "~"), "3", "~"), "4", "~"), "5", "~"), "6", "~"), "7", "~"), "8", "~"), "9", "~"), FIND(", ", Address@row)) - FIND(", ", Address@row) - 3)

Leibel S

@Christina09

This was a fun one :)

Hope it works:

=MID(Address@row, FIND(", ", Address@row) + 2, FIND("~~~~~", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Address@row, "0", "~"), "1", "~"), "2", "~"), "3", "~"), "4", "~"), "5", "~"), "6", "~"), "7", "~"), "8", "~"), "9", "~"), FIND(", ", Address@row)) - FIND(", ", Address@row) - 3)

Christina09

@Leibel S OMG, how do figure out this fast? YOU ARE AWESOME!!! Thank you <3

Morgansims

Hello! How would I edit this formula to find the 2 character state in the following address structure?

111 Alexander St, Memphis, TN 38111 [looking to return "TN"]

TYIA!