Extract digits from string - Google spreadsheet
Solution 1
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+", ""))
Solution 2
These work with integers, decimals and negatives:
=REGEXEXTRACT(A2,"-*\d*\.?\d+")
=VALUE(REGEXEXTRACT(A2,"-*\d*\.?\d+"))
Solution 3
If some of source cells may contain only numbers it is safer to convert the value to text first, then regexreplace. Otherwise it produces error.
Result as text
=REGEXREPLACE(TO_TEXT(C1),"\D+", "")
Result as number
=VALUE(REGEXREPLACE(TO_TEXT(C1),"\D+", ""))
The same for whole column
=ARRAYFORMULA(IF(LEN(C1:C), VALUE(REGEXREPLACE(TO_TEXT(C1:C),"\D+", ""))))
Solution 4
If you wanted to extract with decimal points, you could use regexextract:
=VALUE(REGEXEXTRACT(B4,"[0-9]*\.[0-9]+[0-9]+"))
Example to extract digits, a decimal and 2 significant digits:
=VALUE(REGEXEXTRACT(A1,"[0-9]*\.[0-9]+[0-9]+"))
Output:
Solution 5
array formula variant:
=ARRAYFORMULA(IF(A1:A<>""; REGEXREPLACE(A1:A; "\D+"; )*1; ))
thanos.a
Software Engineer with 10+ years as a technical consultant in retail industry implementing Oracle solutions for leading international retailers. For more please check my linked in profile.
Updated on May 17, 2021Comments
-
thanos.a almost 3 years
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