The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
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: