Search code examples
regexgoogle-sheetscloud

Google sheet : REGEXREPLACE match everything except a particular pattern


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 ...


Solution

  • 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;
    }