Mar 04, 2022 11:52 AM
Hi,
I need help to split this string into four pieces and put them in four new columns…
Maybe split two times with different delimeters?
{“email":"mail@gmail.com”,“firstname”:“John”,“lastname”:“Hong”,“phone”:“1234567”}
Column 1: mail@gmail.com
Column 2: John
Column 3: Hong
Column 4: 1234567
Thanks in advance!
Solved! Go to Solution.
Mar 05, 2022 07:25 PM
Welcome to the Airtable community!
I approached this with regular expressions.
Here is the formula for the email:
REGEX_REPLACE(
REGEX_EXTRACT({text}, '"email":"[^"]+"'),
'("email":")([^"]+)(")',
'$2'
)
Here is the formula for the firstname:
REGEX_REPLACE(
REGEX_EXTRACT({text}, '"firstname":"[^"]+"'),
'("firstname":")([^"]+)(")',
'$2'
)
Hopefully you can figure out the other two fields from these two examples.
Some notes:
Mar 04, 2022 01:36 PM
Unfortunately, Airtable formulas do not have proper string splitting functions or array handlers, so it’s a nightmare trying to do this in a formula. If you really want to attempt it, here are a couple threads that discuss the issue:
If you are on a plan that supports automations, you’ll likely be better off using an automation to parse these values for you, since dealing with JSON is trivial within a Script Action. Sorry to not have better news!
Mar 05, 2022 11:07 AM
Hi, and thanks for answering… do you mean automation with a script or what kind of automation? My plan supports automations.
Mar 05, 2022 12:02 PM
Mar 05, 2022 07:25 PM
Welcome to the Airtable community!
I approached this with regular expressions.
Here is the formula for the email:
REGEX_REPLACE(
REGEX_EXTRACT({text}, '"email":"[^"]+"'),
'("email":")([^"]+)(")',
'$2'
)
Here is the formula for the firstname:
REGEX_REPLACE(
REGEX_EXTRACT({text}, '"firstname":"[^"]+"'),
'("firstname":")([^"]+)(")',
'$2'
)
Hopefully you can figure out the other two fields from these two examples.
Some notes: