Help

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.

Splitting text containing hyphens and underscores to alternate columns

Topic Labels: Formulas
Solved
Jump to Solution
835 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Brooke_Lutz
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello! 

I found the "Splitting text to alternate columns separated by ;" thread and was hopeful that it was exactly what I needed, but after trying to implement this formula I am not getting the expected outcome. I'm not sure if it has to do with the format of my data including a mix of hyphens and underscores or if there is something else I'm missing.

I have anywhere between 1 to 5 lines of text that I need to break out into their own columns instead of being combined. Ideally I would have five new formula columns that allow me to break these out based on them being separated by "\n" or if that it not possible, I can also add a ";" at the end of each line to simplify the formula if it helps!

Example Data:
abc_def-gh_ij-x-kl_mno-pqu-first-msg-x-240101
abc_def-gh_ij-x-kl_mno-pqu-second-msg-x-240102
abc_def-gh_ij-x-kl_mno-pqu-third-msg-x-240103
abc_def-gh_ij-x-kl_mno-pqu-fourth-msg-x-240104
abc_def-gh_ij-x-kl_mno-pqu-fifth-msg-x-240105

A sincere thank you in advance to anyone who takes the team to read this and respond!

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hi @Brooke_Lutz,

Try these formulas.

REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){1}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){2}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){3}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){4}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){5}")

However, I thought Airtable could not use the regular expression non-capturing group, but I was surprised to discover that it could.

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

Hi @Brooke_Lutz,

Try these formulas.

REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){1}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){2}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){3}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){4}")
REGEX_EXTRACT({Data},"(?:\n?([^\n]*)){5}")

However, I thought Airtable could not use the regular expression non-capturing group, but I was surprised to discover that it could.

Brooke_Lutz
5 - Automation Enthusiast
5 - Automation Enthusiast

You are a miracle worker, @Sho! This was exactly what I needed. Thank you so much, I sincerely appreciate your help with this!