Help

Splitting text containing hyphens and underscores to alternate columns

Topic Labels: Formulas
Solved
Jump to Solution
266 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!