CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
IMTheNachoMan
IMTheNachoMan

Reputation: 5811

Extract all matches in a string using REGEXEXTRACT in Google Sheets

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

Upvotes: 4

Views: 3086

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Sample of REGEXREPLACE

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

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

Sample with SPLIT

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

Upvotes: 4

Related Questions