Extract digits from string - Google spreadsheet

Question

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

score 74 · Accepted Answer · answered Jan 19 '17 at 21:47

74

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+", ""))

answered Jan 19 '17 at 21:47

Wiktor Stribiżew

607,720
39
448
563

ZygD · Answer 2 · 2021-05-17T08:33:10.370

28

These work with integers, decimals and negatives:

=REGEXEXTRACT(A2,"-*\d*\.?\d+")
=VALUE(REGEXEXTRACT(A2,"-*\d*\.?\d+"))

edited May 17 '21 at 08:33

answered Jul 30 '19 at 10:51

ZygD

22,092
39
79
102

This is a usefull answer and thansk for posting. However the question is not about extracting numbers but just the digits from 0 to 9. – thanos.a Aug 01 '19 at 07:47
3

I found this question as the top result from my search for how to parse numbers in a Google Sheet. I know that it's not technically the correct answer to the question but it's the answer that I needed. – magikid Dec 16 '19 at 14:33

Kristjan Adojaan · Answer 3 · 2019-12-04T13:28:16.450

5

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+", ""))))

edited Dec 04 '19 at 13:28

answered Dec 04 '19 at 13:17

Kristjan Adojaan

527
7
10

score 2 · Answer 4 · answered Jan 05 '19 at 22:12

2

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:

answered Jan 05 '19 at 22:12

nk abram

1,531
2
11
19

This code works only for special cases like this and it will not for my examples. – thanos.a Jan 06 '19 at 23:15
Thought others might benefit from additional use cases and regextract can be more useful than the above example. – nk abram Jan 07 '19 at 08:31
Might be but solves a different problem such as: "Retrive value from values with unit of measure. I would let admins decide. – thanos.a Jan 08 '19 at 09:32
2

I needed to extract with decimals too, this works in random sentences: =REGEXEXTRACT(H13, "\d+.\d+") – syonip Jan 27 '19 at 17:19

score 1 · Answer 5 · answered Jul 07 '19 at 09:43

1

array formula variant:

=ARRAYFORMULA(IF(A1:A<>""; REGEXREPLACE(A1:A; "\D+"; )*1; ))

answered Jul 07 '19 at 09:43

player0

124,011
12
67
124

score 0 · Answer 6 · answered Jan 19 '17 at 21:48

0

You could do a find and replace (Edit -> Find and Replace), searching for the regex [^\d] (anything not a digit) and replacing with nothing.

answered Jan 19 '17 at 21:48

Alex

12,078
6
64
74

I forgot to add that this needs to be a formula so it will be able to work as data are coming. – thanos.a Jan 19 '17 at 22:03

Extract digits from string - Google spreadsheet

6 Answers6

Linked