Extract whole number from string

[sharky12345]

I'm having issues trying to extract a number from a string.

I've currently got this array formula;

=IF(A11="","",(SUMPRODUCT(MID(0&A11,LARGE(INDEX(ISNUMBER(--MID(A11,ROW($1:$44),1))*ROW($1:$44),0),ROW($1:$44))+1,1)*10^ROW($1:$44)/10)))

The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

GD 12345 GTH1

In this case I'd want to extract 12345 and nothing else, so ignoring any number which is attached to a letter. There will never be more than 1 'string of numbers' with nothing else, but the number of characters either before or after the number I want will vary, so I need to take that into account please.

 

[István Hirsch]

Hi Peter!

All is ok with you?

You're right. Maybe the Normal Formula below Works with all your's suggestion?

=IFERROR(LOOKUP(9^9,--MID(SUBSTITUTE(SUBSTITUTE(A13,"E","A")," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99)),"")

What you think?

Markmzz

Can this cope with "asdf 22 23AUG dfg"?

 

[markmzz]

Can this cope with "asdf 22 23AUG dfg"?

Hi István,

I said "You're right. Maybe the Normal Formula below Works with all your's suggestion"

I'm right or not for all Peter's suggestions in that post?

Anyway, for the value "asdf 22 23AUG dfg" you can use the Normal Formula below:

=IFERROR(LOOKUP(9^9,--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A32,
"A","#"),"E","#"),"O","#"),"U","#")," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99)),"")

Try it and tell me if it Works or not with your example?

By the way, let's wait for the tests of the OP of the post to know what formula or VBA code works or not with the original data.

Markmzz

​

 

[István Hirsch]

Hi István,

I said "You're right. Maybe the Normal Formula below Works with all your's suggestion"

I'm right or not for all Peter's suggestions in that post?

Anyway, for the value "asdf 22 23AUG dfg" you can use the Normal Formula below:

=IFERROR(LOOKUP(9^9,--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A32,
"A","#"),"E","#"),"O","#"),"U","#")," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99)),"")

Try it and tell me if it Works or not with your example?

By the way, let's wait for the tests of the OP of the post to know what formula or VBA code works or not with the original data.

Markmzz

​

Yes, this works with my example, too.

 

[István Hirsch]

Give this a try (confirm with Ctrl - Shift - Enter):

=LOOKUP(9^9,--(TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",300)),300*ROW($1:$50),300))&"."))

 

[Rick Rothstein]

@sharky12345
If you might consider a user-defined function, then you could try this one.

Function ExtractNum(s As String) As Long
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b\d+\b"
  ExtractNum = RX.Execute(s)(0)
End Function

It was not clear to me that the numbers could not have leading zeros that the OP would want to retain, my alternate, non-RegExp function returns the number as Text so as to retain any leading zeros that might exist. I also chose to return the empty text string ("") instead of a #VALUE ! error if no stand-alone number exists (GetNum equals nothing if there is no number).

Function GetNum(S As String) As Variant
  Dim Num As Variant
  For Each GetNum In Split(S & " ")
    If Not GetNum Like "*[!0-9]*" Then Exit Function
  Next
  GetNum = ""
End Function

 

[markmzz]

Give this a try (confirm with Ctrl - Shift - Enter):

=LOOKUP(9^9,--(TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",300)),300*ROW($1:$50),300))&"."))

Nice formula and great suggestion.

Here is my formula of the post #15 with István's suggestion:

=IFERROR(LOOKUP(9^9,--(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99))&".")),"")

Ps: if the formula above didn't work, try &"," and not &"."

Markmzz

 

[Peter_SSs]

In your example, your data is housing 2 numeric values 12345 and 231E2.

No, my example only included one numerical value. "231E2" is a Text string, just like "231X2" or "231F2". It was only your formula that coerced the first into numerical form.
I note your alternative in post #20 , but please abide by #4 of the Forum Rules (last two paragraphs) which ask you to post your solution directly in the forum, not by a link to an external file or to an external image. That way, members who are prevented from or unwilling to use those external links can still have access to your suggestions.

It was not clear to me that the numbers could not have leading zeros that the OP would want to retain, ..

Other than that the thread topic was to extract a "whole number" and that the OP's original formula in post #1 also drops leading zeros if the only "number" in the string was in its own section. The question raised was then how to deal with the situation when digits also appeared in other sections of the string. However, if the question was to extract a string as opposed to a number, my (non-looping) suggestion would become..

Function ExtractNum(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b\d+\b"
  If RX.Test(s) Then ExtractNum = RX.Execute(s)(0)
End Function