Extract all matches in a string using REGEXEXTRACT in Google Sheets

Question

I have a cell with values like so: aasdf123asdf34asdf3

I want to extract all groups of consecutive numbers: 123, 34, and 3.

I think this is the regular expression I need: (\d+).

But it is only extracting the first match.

This works outside of Google Sheets. Not sure why I can't get it to work in Google Sheets.

https://regexr.com/572et

Answer 1

You could try actually generating the CSV string you want directly, using REGEXREPLACE:

=REGEXREPLACE(REGEXREPLACE("aasdf123asdf34asdf3", "\D+", ","), "^,|,$", "")

The inner call to REGEXREPLACE replaces all clusters of non digit characters with comma. The outer call then removed any leading/trailing commas which the first replacement might have left behind.

---

Moreover you can use SPLIT to separate the values into each individual cell:

=TRANSPOSE( SPLIT(REGEXREPLACE(REGEXREPLACE("aasdf123asdf34asdf3", "\D+", ","), "^,|,$", ""), ","))

In here the TRANSPOSE function is just to stack the matches vertically instead of horizontally as SPLIT would lay them as default.