Formula to extract State & Zip from Address

Platform discussions Special workflows & use cases
1

I have a Text column where addresses are formatted like this:

38 Toonami St, New York, NY 10001, USA

What I need to do is have all parts of the address split into 4 separate plain text columns: Street Address, City, State, and ZIP.

Is there a way to do this via formula? (Or perhaps, via formula & the General Caster app?)

I’m no good with formulas, and so far I’ve only been able to get State and ZIP by using several Formula columns and progressively whittling down the string via RIGHT() and LEFT() to get the bits I need, but there’s no way that’s the most efficient method.

Thanks!

2

Location = text
Street Address = LEFT({Location},FIND(“, “,{Location})-1)
City = LEFT(SUBSTITUTE({Location},{Street Address}&”, “,””),FIND(“, “,SUBSTITUTE({Location},{Street Address}&”, “,””))-1)
State = LEFT(SUBSTITUTE({Location},{Street Address}&“, “&{City}&”, “,””),2)
ZIP = LEFT(SUBSTITUTE({Location},{Street Address}&“, “&{City}&”, “&{State}&” “,””),FIND(“, “,SUBSTITUTE({Location},{Street Address}&”, “&{City}&”, “&{State}&” “,””))-1)