How can I separate a string by underscore (_) in google spreadsheets using regex?

Question

I need to create some columns from a cell that contains text separated by "_".

The input would be: campaign1_attribute1_whatever_yes_123421

And the output has to be in different columns (one per field), with no "_" and excluding the final number, as it follows: campaign1 attribute1 whatever yes

It must be done using a regex formula!

help!

Thanks in advance (and sorry for my english)

The answer to this Q was here: http://stackoverflow.com/questions/26137115/arrayformula-does-not-work-with-split — Max Makhrov, Mar 15 '17 at 07:24

BioGenX · Answer 1 · 2017-03-15T17:00:16.667

To solve this you can use the SPLIT and REGEXREPLACE functions

Solution:

Text      -  A1 = "campaign1_attribute1_whatever_yes_123421"
Formula   -  A3 = =SPLIT(REGEXREPLACE(A1,"_+\d*$",""), "_", TRUE)

Explanation:

In cell A3 We use SPLIT(text, delimiter, [split_by_each]), the text in this case is formatted with regex =REGEXREPLACE(A1,"_+\d$","")* to remove 123421, witch will give you a column for each word delimited by ""

A1 = "campaign1_attribute1_whatever_yes_123421"
A2 = "=REGEXREPLACE(A1,"_+\d*$","")" //This gives you : *campaign1_attribute1_whatever_yes*
A3 = SPLIT(A2, "_", TRUE) //This gives you: campaign1 attribute1 whatever yes, each in a separate column.

Please provide some sort of explanation or narrative about your answer. Even if it may be correct, just a straight answer like this is often useless. — m_callens, Mar 15 '17 at 16:48

score 1 · Answer 2 · answered Mar 15 '17 at 07:18

1

=REGEXEXTRACT("campaign1_attribute1_whatever_yes_123421","(("&REGEXREPLACE("campaign1_attribute1_whatever_yes_123421","((_)|(\d+$))",")$1(")&"))")

What this does is replace all the _ with parenthesis to create capture groups, while also excluding the digit string at the end, then surround the whole string with parenthesis.

We then use regex extract to actuall pull the pieces out, the groups automatically push them to their own cells/columns

answered Mar 15 '17 at 07:18

Aurielle Perlmann

5,323
1
15
26

to convert it into arrayFormula: `=ArrayFormula(REGEXEXTRACT(A1:A13,"(("&REGEXREPLACE(A1:A13,"((_)|(\d+$))",")$1(")&"))"))` – Max Makhrov Mar 15 '17 at 07:22

score 0 · Answer 3 · edited Apr 13 '17 at 13:00

I finally figured it out yesterday in stackoverflow (spanish): https://es.stackoverflow.com/questions/55362/c%C3%B3mo-separo-texto-por-guiones-bajos-de-una-celda-en...

It was simple enough after all...

The reason I asked to be only in regex and for google sheets was because I need to use it in Google data studio (same regex functions than spreadsheets)

To get each column just use this regex extract function:

1st column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){0}([^_]*)_')

2nd column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){1}([^_]*)_')

3rd column: REGEXP_EXTRACT(Campaña, '^(?:[^_]*_){2}([^_]*)_')

etc... The only thing that has to be changed in the formula to switch columns is the numer inside {}, (column number - 1). If you do not have the final number, just don't put the last "_".

Lastly, remember to do all the calculated fields again, because (for example) it gets an error with CPC, CTR and other Adwords metrics that are calculated automatically.

Hope it helps!

How can I separate a string by underscore (_) in google spreadsheets using regex?

3 Answers3