Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 12, 2022 01:26 AM
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! Go to Solution.
Aug 12, 2022 09:14 AM
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.
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'^.+\\('
),
'\s\($',
''
)
)
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\(.+\)'
),
'[\(\)]',
''
)
)
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.
Aug 12, 2022 06:03 AM
Checkout 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)
Aug 12, 2022 09:14 AM
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.
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'^.+\\('
),
'\s\($',
''
)
)
IF(
{String},
REGEX_REPLACE(
REGEX_EXTRACT(
{String},
'\(.+\)'
),
'[\(\)]',
''
)
)
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.
Aug 12, 2022 10:26 AM
Haven’t tried wrapping my head around REGEX, seems useful but looks so confusing.
Aug 12, 2022 10:58 AM
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!
Aug 12, 2022 11:15 PM
Ben, you are the best. I spent way more than 30 minutes, and regex still let my head explode. Will try your examples today.
Aug 13, 2022 08:21 AM
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.
Aug 14, 2022 05:50 PM
Thanks I’ll take a look and try to figure it out!
Jan 19, 2023 03:35 AM - edited Jan 19, 2023 03:37 AM
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 )