I have one column with text in the following format:
A Meeting By The River (Album) 3 versions
I need to split it into 3 columns
1: the string before the bracket
2: the string between the bracket
3: the string after the bracket
Solved
Extracting or deleting string in brackets
Best answer by Ben_Young1
For diversity of thought, if you’re like me and dislike handling the FIND/MID/LEFT/RIGHT dance, you can utilize regex.
Here’s a super quick array of formulas that produce the desired behavior.
- Before:
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'^.+\\('
),
'\s\($',
''
)
)
- Middle
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\(.+\)'
),
'[\(\)]',
''
)
)
- After
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\).+$'
),
'\)',
''
)
)
I just woke up and wrote those pretty fast, so the patterns might be wonky, but they work and serve as an example.
I almost always use regex as it allows me to adjust the pattern to account for changing requirements easily.
All I have to do is look at the pattern, and I can instantly troubleshoot exactly what it’s looking for in the target parameter.
+17Checkout Airtable Formula field reference and the formula playground. These function would also be very similar or the same in google sheets or excel, which there are lots of examples for online.
Here’s one way to do it. (replace {Main Text} with your field name in the formulas below.)
1: the string before the bracket
Create new Field/Column
LEFT({Main Text}, FIND(“(”, {Main Text})-1)
2: the string between the bracket
Create new Field/Column
MID({Main Text},FIND(“(”,{Main Text})+1, FIND(“)”,{Main Text})-FIND(“(”,{Main Text})-1)
3: the string after the bracket
Create new Field/Column
RIGHT({Main Text}, LEN({Main Text})-FIND(“)”,{Main Text})-1)
+22For diversity of thought, if you’re like me and dislike handling the FIND/MID/LEFT/RIGHT dance, you can utilize regex.
Here’s a super quick array of formulas that produce the desired behavior.
- Before:
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'^.+\\('
),
'\s\($',
''
)
)
- Middle
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\(.+\)'
),
'[\(\)]',
''
)
)
- After
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\).+$'
),
'\)',
''
)
)
I just woke up and wrote those pretty fast, so the patterns might be wonky, but they work and serve as an example.
I almost always use regex as it allows me to adjust the pattern to account for changing requirements easily.
All I have to do is look at the pattern, and I can instantly troubleshoot exactly what it’s looking for in the target parameter.
+17For diversity of thought, if you’re like me and dislike handling the FIND/MID/LEFT/RIGHT dance, you can utilize regex.
Here’s a super quick array of formulas that produce the desired behavior.
- Before:
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'^.+\\('
),
'\s\($',
''
)
)
- Middle
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\(.+\)'
),
'[\(\)]',
''
)
)
- After
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\).+$'
),
'\)',
''
)
)
I just woke up and wrote those pretty fast, so the patterns might be wonky, but they work and serve as an example.
I almost always use regex as it allows me to adjust the pattern to account for changing requirements easily.
All I have to do is look at the pattern, and I can instantly troubleshoot exactly what it’s looking for in the target parameter.
Haven’t tried wrapping my head around REGEX, seems useful but looks so confusing.
+22Haven’t tried wrapping my head around REGEX, seems useful but looks so confusing.
Regex can be a bit intimidating.
I don’t blame you.
Once you put like 20-30 minutes into exploring it a bit, you will find that it’s quite intuitive.
There are a ton of dope cheat sheets for quick reference, but tools like this are amazing at allowing you to visualize exactly what’s happening in real-time.
If you’re at all interested in programming, it’s a key part of working with strings. It definitely comes in handy when working with data overall.
If you’d like, I’d be happy to break down the formulas I posted and walk you through how the regex I used works!
Regex can be a bit intimidating.
I don’t blame you.
Once you put like 20-30 minutes into exploring it a bit, you will find that it’s quite intuitive.
There are a ton of dope cheat sheets for quick reference, but tools like this are amazing at allowing you to visualize exactly what’s happening in real-time.
If you’re at all interested in programming, it’s a key part of working with strings. It definitely comes in handy when working with data overall.
If you’d like, I’d be happy to break down the formulas I posted and walk you through how the regex I used works!
Ben, you are the best. I spent way more than 30 minutes, and regex still let my head explode. Will try your examples today.
+29For diversity of thought, if you’re like me and dislike handling the FIND/MID/LEFT/RIGHT dance, you can utilize regex.
Here’s a super quick array of formulas that produce the desired behavior.
- Before:
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'^.+\\('
),
'\s\($',
''
)
)
- Middle
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\(.+\)'
),
'[\(\)]',
''
)
)
- After
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\).+$'
),
'\)',
''
)
)
I just woke up and wrote those pretty fast, so the patterns might be wonky, but they work and serve as an example.
I almost always use regex as it allows me to adjust the pattern to account for changing requirements easily.
All I have to do is look at the pattern, and I can instantly troubleshoot exactly what it’s looking for in the target parameter.
I also prefer REGEX for these situations.
I find some REGEX patterns need extensive testing. That is in part why I decided to not try writing a formula for this thread. I am glad that you did, though.
Many people need more than 30 minutes, and some people never find REGEX to be intuitive. Understanding a complex REGEX requires a level of analytical thinking and attention to detail that I have not needed anywhere else.
+17Regex can be a bit intimidating.
I don’t blame you.
Once you put like 20-30 minutes into exploring it a bit, you will find that it’s quite intuitive.
There are a ton of dope cheat sheets for quick reference, but tools like this are amazing at allowing you to visualize exactly what’s happening in real-time.
If you’re at all interested in programming, it’s a key part of working with strings. It definitely comes in handy when working with data overall.
If you’d like, I’d be happy to break down the formulas I posted and walk you through how the regex I used works!
Thanks I’ll take a look and try to figure it out!
+11Checkout Airtable Formula field reference and the formula playground. These function would also be very similar or the same in google sheets or excel, which there are lots of examples for online.
Here’s one way to do it. (replace {Main Text} with your field name in the formulas below.)
1: the string before the bracket
Create new Field/Column
LEFT({Main Text}, FIND(“(”, {Main Text})-1)
2: the string between the bracket
Create new Field/Column
MID({Main Text},FIND(“(”,{Main Text})+1, FIND(“)”,{Main Text})-FIND(“(”,{Main Text})-1)
3: the string after the bracket
Create new Field/Column
RIGHT({Main Text}, LEN({Main Text})-FIND(“)”,{Main Text})-1)
Simpler regex version:
Before bracket:
REGEX_EXTRACT(Name, "^[^\\(]+") - match from beginning up to first (
Middle:
REGEX_EXTRACT(Name, "\\(.+?\\)") - match everything between ( and )
End:
REGEX_EXTRACT(Name, "[^\\)]+$") - match from end backwards to first )
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.