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

Answer 1

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.

Answer 2

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 "))