Google sheet : REGEXREPLACE match everything except a particular pattern

Question

I would try to replace everything inside this string :

[JGMORGAN - BANK2] n° 10 NEWYORK, n° 222 CAEN, MONTELLIER, VANNES / TARARTA TIs 1303222074, 1403281851 & 1307239335 et Cloture TIs 1403277567, 1410315029

Except the following numbers : 1303222074 1403281851 1307239335 1403277567 1410315029

I have built a REGEX to match them :

1[0-9]{9}

But I have not figured it out to do the opposite that is everything except all matches ...

You should be sure to search before asking questions. This question has been answered on this site already. Search for "invert regular expression" — dub stylee, Feb 27 '15 at 22:43

Casimir et Hippolyte · Answer 1 · 2015-02-28T00:01:59.133

1

google spreadsheet use the Re2 regex engine and doesn't support many usefull features that can help you to do that. So a basic workaround can help you:

match what you want to preserve first and capture it:

pattern: [0-9]*(?:[0-9]{0,9}[^0-9]+)*(?:([0-9]{9,})|[0-9]*\z)

replacement: $1 (with a space after)

demo

So probably something like this:

=TRIM(REGEXREPLACE("[JGMORGAN - BANK2] n° 10 NEWYORK, n° 222 CAEN, MONTELLIER, VANNES / TARARTA TIs 1303222074, 1403281851 & 1307239335 et Cloture TIs 1403277567, 1410315029"; "[0-9]*(?:[0-9]{0,9}[^0-9]+)*(?:([0-9]{9,})|[0-9]*\z)"; "$1 "))

edited Feb 28 '15 at 00:01

answered Feb 27 '15 at 22:50

Casimir et Hippolyte

88,009
5
94
125

I tried to do this : =regexreplace("[JGMORGAN - BANK2] n° 10 NEWYORK, n° 222 CAEN, MONTELLIER, VANNES / TARARTA TIs 1303222074, 1403281851 & 1307239335 et Cloture TIs 1403277567, 1410315029"; "[^0-9]*([0-9]{9,})[^0-9]*|[0-9]+[^0-9]*|[^0-9]*\z" ; " " ) but it displays [JGMORGAN - BANK . So it does not work – Bastien Feb 27 '15 at 22:58
@Bastien: unfortunatly, I can't test the pattern with google spreadsheet, but refresh your browser, my answer has changed and use one of the good replacement string. Perhaps you will need to adjust the syntax but the idea is here. – Casimir et Hippolyte Feb 27 '15 at 23:00
@Bastien: you don't seem to understand how it works. see this link: https://regex101.com/r/kB3pK2/1 all you need is to trim the result after. – Casimir et Hippolyte Feb 27 '15 at 23:05
I admit Im a bit lost on that. I check your work via the link mentionned but all I can say is when I do a =REGEXEXTRACT(K25; "[^0-9]*(?:([0-9]{9,})|[0-9]+|\z)") on the string (K25 is a cell containing the string in the example above), it extracts nothing at all (but there is no error though) – Bastien Feb 27 '15 at 23:10
Dude, thank you but just a quick fix : =TRIM(REGEXREPLACE("[JGMORGAN - BANK2] n° 10 NEWYORK, n° 222 CAEN, MONTELLIER, VANNES / TARARTA TIs 1303222074, 1403281851 & 1307239335 et Cloture TIs 1403277567, 1410315029"; "[0-9]*(?:[0-9]{0,9}[^0-9]+)*(?:([0-9]{9,})|\z)"; "$1 ")) (';' instead of ',') – Bastien Feb 27 '15 at 23:37
@Bastien: what a strange syntax. – Casimir et Hippolyte Feb 27 '15 at 23:38
Unfortunately, I spoke too soon. I though It would work with other strings like this one for instance : "[SOCIETE - AUTRE] N76 - PORT \ Mot Test N°1409307426 \Perte de visu de la borne02" but I get this : "1409307426 \Perte de visu de la borne02" instead of only 1409307426. I have other examples which do not work with your regex ... Example 2 : "[TESTST- STTTT-FFF] / Suite TI N° 1403280176 / Etat du XXX au 12/02/2015" Example 3 : "[ DUD- GSFSF CCSFS] / New incident ticket n° 1502335953 / APs "AP-XXFFDFFE-A08421", "AP-XXFFDFFE-A08639", "AP-XXFFDFFE-A032448" are down" – Bastien Feb 27 '15 at 23:51
@Bastien: try with this change. – Casimir et Hippolyte Feb 28 '15 at 00:04

score 1 · Answer 2 · answered May 14 '16 at 09:11

You can also do this with dynamic native functions:

=REGEXEXTRACT(A1,rept("(\d{10}).*",counta(split(regexreplace(A1,"\d{10}","@"),"@"))-1))

basically it is first split by the desired string, to figure out how many occurrences there are of it, then repeats the regex to dynamically create that number of capture groups, thus leaving you in the end with only those values.

score 0 · Accepted Answer · edited Apr 05 '21 at 11:06

First of all thank you Casimir for your help. It gave me an idea that will not be possible with a built-in functions and strong regex lol. I found out that I can make a homemade function for my own purposes (yes I'm not very "up to date"). It's not very well coded and it returns doublons. But rather than fixing it properly, I use the built in UNIQUE() function on top of if to get rid of them; it's ugly and I'm lazy but it does the job, that is, a list of all matches of on specific regex (which is: 1[0-9]{9}). Here it is:

function ti_extract(input) {
  var tab_tis = new Array();
  var tab_strings = new Array();


  tab_tis.push(input.match(/1[0-9]{9}/)); // get the TI and insert in tab_tis

  var string_modif = input.replace(tab_tis[0], " "); // modify source string (remove everything except the TI)
  tab_strings.push(string_modif); // insert this new string in the table

  var v = 0;
  var patt = new RegExp(/1[0-9]{9}/);
  var fin = patt.test(tab_strings[v]);

  var first_string = tab_strings[v];


  do {
    first_string = tab_strings[v]; // string 0, or the string with the first removed TI
    tab_tis.push(first_string.match(/1[0-9]{9}/)); // analyze the string and get the new TI to put it in the table

    var string_modif2 = first_string.replace(tab_tis[v], " "); // modify the string again to remove the new TI from the old string
    tab_strings.push(string_modif2);

    v += 1;
  }
  while(v < 15)

  return tab_tis;
}

Google sheet : REGEXREPLACE match everything except a particular pattern

3 Answers3

Linked