Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Split text into new columns, unclear delimeter

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
3071
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

- I had to change some curly quotes to straight quotes. I’m guessing that your original text has straight quotes because it looks like a JSON string, which uses straight quotes. Often when pasting in text to the forum, the forum software converts straight quotes to curly quotes.
- This formula assumes that there will be data. If there is missing data, you will get an error. If any of the fields might be missing, the formula can be adjusted to return blank.
- This formula assumes that there will be no double quotes inside any of the actual values.
- This formula assumes that the spacing will be consistent (no spaces except inside actual values).
- This formula assumes that things that look like numbers are actually strings, as is the case with “phone”. If you have an actual number, you will need a slightly different formula.
- If you want any of the more complex formulas that I describe, feel free to book at meeting with me.

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 05, 2022 11:07 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 05, 2022 12:02 PM

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

- I had to change some curly quotes to straight quotes. I’m guessing that your original text has straight quotes because it looks like a JSON string, which uses straight quotes. Often when pasting in text to the forum, the forum software converts straight quotes to curly quotes.
- This formula assumes that there will be data. If there is missing data, you will get an error. If any of the fields might be missing, the formula can be adjusted to return blank.
- This formula assumes that there will be no double quotes inside any of the actual values.
- This formula assumes that the spacing will be consistent (no spaces except inside actual values).
- This formula assumes that things that look like numbers are actually strings, as is the case with “phone”. If you have an actual number, you will need a slightly different formula.
- If you want any of the more complex formulas that I describe, feel free to book at meeting with me.

Reply