Help! stuck for 3 days

[ben_pappas]

I need some help. I have a column of address like below:

12345 Anywhere St, San Fransico, CA, 90000

I need to add the street 12345 Anywhere to is own column called street. Same with city ect. whoever can solve has a VENm0 coming with 25 dolls.

[Jeremy_Oglesby]

Remove and keep first value (street) in its own field:

IF(FIND(",",{Address Field}&''),TRIM(SUBSTITUTE(LEFT({Address Field}&'',SEARCH(",",{Address Field}&'')),",","")),{Address Field})

Remove and keep everything after the street in its own field:

IF(FIND(",",{Address Field}&''),TRIM(MID({Address Field}&'',SEARCH(",",{Address Field}&'')+1,500)))

Remove and keep the next value (city) in its own field:

IF(FIND(",",{Address Street Removed}&''),TRIM(SUBSTITUTE(LEFT({Address Street Removed}&'',SEARCH(",",{Address Street Removed}&'')),",","")),{Address Street Removed})

Remove and keep everything after the city in its own field:

IF(FIND(",",{Address Street Removed}&''),TRIM(MID({Address Street Removed}&'',SEARCH(",",{Address Street Removed}&'')+1,500)))

Remove and keep next value (state) in its own field:

IF(FIND(",",{Address Street & City Removed}&''),TRIM(SUBSTITUTE(LEFT({Address Street & City Removed}&'',SEARCH(",",{Address Street & City Removed}&'')),",","")),{Address Street & City Removed})

Remove and keep everything after state (zip) in its own field:

IF(FIND(",",{Address Street & City Removed}&''),TRIM(MID({Address Street & City Removed}&'',SEARCH(",",{Address Street & City Removed}&'')+1,500)))

You can likely stop there… if you have more comma-separated values to split out, just continue the pattern.

[Zach_Young]

@Jeremy_Oglesby’s approach will work for the specific case where street and city are always delimited by the first comma, but that may break down with other data that isn’t so consistent.

Parsing an address should really be done by an Address Validator. I’m a big fan of SmartyStreets: they offer 250 free address validations per month, and have an online bulk validator where you can paste CSV data in, validate it, and copy all validated and parsed data back out. And for $50 (twice your Venmo reward) you can validate 5000 addresses.

[Jeremy_Oglesby]

Keep in mind, I wrote this tedious, formula-based solution nearly 2 years ago, when the Scripting app and Custom apps were only a twinkle in Airtable’s eye.