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)