Search code examples
regexgoogle-sheetsre2

How to make a regex expression that will extract all cell numbers from a comma separated list of cells and subtract a constant value from each one?

I have a cell with a list of cells that fulfil a certain condition. D3,D4,D5,D6 as an example. I have to use google sheet formulas to extract just the numbers and commas from the cell, while also subtracting 2 from each number.

I figured REGEXEXTRACT would be the only formula that would allow me to get what I want - I was however snagged, because REGEXEXTRACT uses RE2 (unlike Google Apps Script, which uses regular Regex).

I managed to figure out that REGEXEXTRACT(F2, "(d+,)") will retrieve the first number and comma from the cell, and in regular regex I'd need just use the g flag to get the rest of them - I've been looking for that flag in the RE2 syntax, but I couldn't find it.

EDIT: A link to a spreadsheet

Input: D3,D4,D5,D6

Output: 1,2,3,4

Solution

  • In F10 I entered this formula

    =join(","; ArrayFormula(regexextract(split(F4; ","); "\d+")-2))

    See if that works for you?