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

Have you tried the solution offered? Did it work for you? If not we should try finding out why. If yes, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. — marikamitsos

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2020-06-20T05:43:14

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.

Extract all matches in a string using REGEXEXTRACT in Google Sheets

1 Answers