Splitting text containing hyphens and underscores to alternate columns

[Brooke_Lutz]

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!

[Sho · Accepted solution]

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]

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