Help

Re: Looking for a Airtbale pro asap

959 0
cancel
Showing results for 
Search instead for 
Did you mean: 
ben_pappas
5 - Automation Enthusiast
5 - Automation Enthusiast

Willing to Venmo for a formula I need. I have a list of incoming address as follows

1234 anywhere st, San Diego, CA 90001

I need to execute a text to column function based on commas.

3 Replies 3

I tried to respond on your other post, but my response is pending approval for some reason. I’ll try again here…

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.

Jennifer_j
4 - Data Explorer
4 - Data Explorer

Hi,

I can help. Please check PM.

Thanks,
Jennifer J

ben_pappas
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks everyone this has been resolved!