Multiple regex matches in Google Sheets formula

10
votes

I'm trying to get the list of all digits preceding a hyphen in a given string (let's say in cell A1), using a Google Sheets regex formula :

=REGEXEXTRACT(A1, "\d-")

My problem is that it only returns the first match... how can I get all matches?

Example text:

"A1-Nutrition;A2-ActPhysiq;A2-BioMeta;A2-Patho-jour;A2-StgMrktg2;H2-Bioth2/EtudeCas;H2-Bioth2/Gemmo;H2-Bioth2/Oligo;H2-Bioth2/Opo;H2-Bioth2/Organo;H3-Endocrino;H3-Génétiq"

My formula returns 1-, whereas I want to get 1-2-2-2-2-2-2-2-2-2-3-3- (either as an array or concatenated text).

I know I could use a script or another function (like SPLIT) to achieve the desired result, but what I really want to know is how I could get a re2 regular expression to return such multiple matches in a "REGEX.*" Google Sheets formula. Something like the "global - Don't return after first match" option on regex101.com

I've also tried removing the undesired text with REGEXREPLACE, with no success either (I couldn't get rid of other digits not preceding a hyphen).

Any help appreciated! Thanks :)

5
regexgoogle-sheetsre2
I think you are otta luck on that. Documentation says only the first is returned... - dawg
@dawg Thanks, I agree, but I think there must be a way to define the right regex to get all matches; for instance if I use ((\d-)) I get the first two matches, with (((\d-))) I get the first three, but how to get them all, not knowing how many there are? Maybe using group name \1, allowing for any match repetition separated by .* ...or at least by combining multiple REGEXEXTRACT and/or REGEXREPLACE formulas. - flo5783
I tried using (?g) which works for some re flavors but not re2 I am afraid. It is a great question. - dawg
I like the two answers using capture groups. Even simpler, =regexreplace(A1,"(\d-)|.","$1") seems to work. - Tom Sharpe
Yes I think =regexreplace(A1,"(\d-)|.","$1") is the best one so far, thanks! Simple and efficient :) - flo5783

5 Answers

10
votes

You can actually do this in a single formula using regexreplace to surround all the values with a capture group instead of replacing the text:

=join("",REGEXEXTRACT(A1,REGEXREPLACE(A1,"(\d-)","($1)")))

basically what it does is surround all instances of the \d- with a "capture group" then using regex extract, it neatly returns all the captures. if you want to join it back into a single string you can just use join to pack it back into a single cell:

enter image description here

6
votes

You may create your own custom function in the Script Editor:

function ExtractAllRegex(input, pattern,groupId) {
  return Array.from(input.matchAll(new RegExp(pattern,'g')), x=>x[groupId]);
}

Or, if you need to return all matches in a single cell joined with some separator:

function ExtractAllRegex(input, pattern,groupId,separator) {
  return Array.from(input.matchAll(new RegExp(pattern,'g')), x=>x[groupId]).join(separator);
}

Then, just call it like =ExtractAllRegex(A1, "\d-", 0, ", ").

Description:

  • input - current cell value
  • pattern - regex pattern
  • groupId - Capturing group ID you want to extract
  • separator - text used to join the matched results.
3
votes

I wasn't able to get the accepted answer to work for my case. I'd like to do it that way, but needed a quick solution and went with the following:

Input:

1111 days, 123 hours 1234 minutes and 121 seconds

Expected output:

1111 123 1234 121

Formula:

=split(REGEXREPLACE(C26,"[a-z,]"," ")," ")
3
votes

Edit

I came up with more general solution:

=regexreplace(A1,"(.)?(\d-)|(.)","$2")

It replaces any text except the second group match (\d-) with just the second group $2.

"(.)?(\d-)|(.)"
  1    2    3  
  Groups are in ()
  ---------------------------------------
 "$2" -- means return the group number 2

Learn regular expressions: https://regexone.com

Try this formula:

=regexreplace(regexreplace(A1,"[^\-0-9]",""),"(\d-)|(.)","$1")

It will handle string like this:

"A1-Nutrition;A2-ActPhysiq;A2-BioM---eta;A2-PH3-Généti***566*9q"

with output:

1-2-2-2-3-

2
votes

This seems to work and I have tried to verify it.

The logic is

(1) Replace letter followed by hyphen with nothing

(2) Replace any digit not followed by a hyphen with nothing

(3) Replace everything which is not a digit or hyphen with nothing

=regexreplace(A1,"[a-zA-Z]-|[0-9][^-]|[a-zA-Z;/é]","")

Result

1-2-2-2-2-2-2-2-2-2-3-3-

Analysis

I had to step through these procedurally to convince myself that this was correct. According to this reference when there are alternatives separated by the pipe symbol, regex should match them in order left-to-right. The above formula doesn't work properly unless rule 1 comes first (otherwise it reduces all characters except a digit or hyphen to null before rule (1) can come into play and you get an extra hyphen from "Patho-jour").

Here are some examples of how I think it must deal with the text

enter image description here