Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 21, 2019 07:03 PM
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.
Dec 22, 2019 03:01 PM
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.
Dec 22, 2019 11:16 PM
Hi,
I can help. Please check PM.
Thanks,
Jennifer J
Dec 23, 2019 11:02 AM
Thanks everyone this has been resolved!