2

How could I extract only the numbers from a text string in Excel or Google Sheets? For example:

A1 - a1b23eg67
A2 - 15dgrgr156

Result desired is 

B1 - 12367
B2 - 15156
halfer
  • 19,824
  • 17
  • 99
  • 186
Luis
  • 175
  • 1
  • 2
  • 11
  • 3
    If the problem is solved, please, accept one of the answers. You can tick green mark to do it. See: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work. Then anyone would see it was solved so you don't need to change the name of question to "SOLVED..." – Max Makhrov Apr 26 '17 at 13:24

4 Answers4

7

You can do it with capture groups in Google Sheets

=REGEXREPLACE(A1,ʺ(\d)|.ʺ,ʺ$1ʺ)

Anything which matches the contents of the brackets (a digit) will be copied to the output, anything else replaced by an empty string.

Please see @Max Makhrov's answer to this question

or

=regexreplace(A1,ʺ[^\d]ʺ,ʺʺ)

to remove anything which isn't a digit.

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thank you, The second formula worked in the desired way. – Luis Apr 26 '17 at 13:09
  • both work if the [double prime characters](http://www.fileformat.info/info/unicode/char/02ba/index.htm) `ʺ` are replaced with quotation marks `"`, and `"[^\d]"` can be shortened to `"\D+"` (or `"[^0-9]+"` to avoid matching [Unicode and digits in other languages](http://www.fileformat.info/info/unicode/category/Nd/list.htm)) – Slai Dec 01 '17 at 14:15
3

Because you asked for Excel also,

If you have a subscription to office 365 Excel then you can use this array formula:

=--TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
1

I would imagine there is a way to pull this off with =RegexExtract but I can't figure out how to get it to repeat the search after the first hit. Often with these regex function implementations there is a third parameter to repeat, but it doesn't look like google implemented it.

At any rate, the following formula will do the trick. It's just a little roundabout:

=concatenate(SPLIT( LOWER(A1) , "abcdefghijklmnopqrstuvwxyz" ))

This is converting the string to lower case, then splitting the string using any letter of the alphabet. This will return an array of the numbers left over, which we concatenate back together.

Update, switched over to =REGEXREPLACE() instead of extract...:

=regexreplace(A1, "[a-z]", "")

That's a much cleaner and obvious way of doing it than that concat(split()) nonsense.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • I have used: =REGEXEXTRACT(A1,"([0-9]*\s+[0-9]*\,[0-9]*)") To extract the numbers from the followig example: €25 000,00 result was: 25 00,00 However could get arround when the numbers and letters do not follow a patern. – Luis Apr 26 '17 at 12:55
  • What about when special characters appear also, such as white spaces or €&/? Could that be done also? – Luis Apr 26 '17 at 13:03
  • Take a look at Tom's answer. The regex expression "[^\d]" will do the job. `\d` means any numeric character and `^` means "Not". So basically `=regexreplace(A1, "[^/d]", "")` says replace any character that is not a number with a space in cell `A1`. – JNevill Apr 26 '17 at 13:11
0

Please try this formula it will surely resolve you problem =REGEXEXTRACT(A1,"\d+")

Sarjan Sailani
  • 1
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 10 '23 at 17:33