Extract digits from string - Google spreadsheet

regex google-sheets extract google-sheets-formula array-formulas
thanos.a picture thanos.a · Jan 19, 2017 · Viewed 49k times · Source

In Google spreadsheets, I need a formula to extract all digits (0 to 9) contained into an arbitrary string, that might contain any possible character and put them into a single cell.

Examples (Input -> Output)

d32Ελληνικάfe9j.r/3-fF66 -> 329366
h01j2j3jFxF$$4j5j6j7j8j9 -> 0123456789

Answer

Wiktor Stribiżew picture Wiktor Stribiżew · Jan 19, 2017

You may replace all non-digit characters using the \D+ regex and an empty string replacement with

=REGEXREPLACE(A11,"\D+", "")

or with casting it to a number:

=VALUE(REGEXREPLACE(A11,"\D+", ""))

enter image description here

Related questions

How do I combine COUNTIF with OR

In Google Spreadsheets, I need to use the COUNTIF function on a range with multiple criteria. So in the table below, I would need to have something like =COUNTIF(B:B,"Mammal"or"Bird") and return a value of 4. A |…

Read More
find & replace commas with newline on Google Spreadsheet

I've been trying to figure out how to replace commas with line-breaks. I've tried using (/r/n/), char(10), the Unicode representation (can't remember now), but it rather predictably replaces the comma with whatever I type in the other box, not …

Read More
Split string and get last element

Let's say I have a column which has values like: foo/bar chunky/bacon/flavor /baz/quz/qux/bax I.e. a variable number of strings separated by /. In another column I want to get the last element from each …

Read More