# Extract numbers from string

C

#### caroline

hello,
I am using the following code to extract numbers from a string
http://www.ozgrid.com/VBA/ExtractNum.htm
however when I have strings like WS123ABC45cft, I would like to extract only
the first set of number"123". The code provided extracts "12345"
Do you know how to correct the code?
Please note that the number of numbers and letters are variables so I cannot
use LEFT or RIGHT.
thanks

G

#### Gary''s Student

Function numit(r As Range) As Double
Dim s As String, s2 As String, c As String
Dim b As Boolean
b = False
s2 = ""
s = r.Value
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c Like "#" Then
s2 = s2 & c
b = True
Else
If b = True Then Exit For
End If
Next
numit = --s2
End Function

C

#### cht13er

hello,
I am using the following code to extract numbers from a stringhttp://www.ozgrid.com/VBA/ExtractNum.htm
however when I have strings like WS123ABC45cft, I would like to extract only
the first set of number"123". The code provided extracts "12345"
Do you know how to correct the code?
Please note that the number of numbers and letters are variables so I cannot
use LEFT or RIGHT.
thanks

If you need to you can use left and right ... because it's easy to get
the length of a string ( = len(strText) )

Why don't you use this little bit of code if you're never going to see
decimals and negative signs ...

Option Explicit

Private Sub GetFirstNumber()

Dim strText As String
Dim I As Integer
Dim bnOK As Boolean

strText = "WS123ABC45cft"
I = 1
bnOK = False

Do Until IsNumeric(strText) = True
If IsNumeric(Mid(strText, I, 1)) = False Then
If bnOK = False Then
strText = Mid(strText, 2)
Else
strText = Left(strText, I - 1)
End If
Else
I = I + 1
bnOK = True
End If
Loop

End Sub

Of course changing my hard-coded strText = "WS123ABC45cft" with a
cell ...

cheers

Chris

C

#### cht13er

hello,
I am using the following code to extract numbers from a stringhttp://www.ozgrid.com/VBA/ExtractNum.htm
however when I have strings like WS123ABC45cft, I would like to extract only
the first set of number"123". The code provided extracts "12345"
Do you know how to correct the code?
Please note that the number of numbers and letters are variables so I cannot
use LEFT or RIGHT.
thanks

Note: if you like, you could add the code as a public function ...
then when you're in Excel you can just enter in " =getfirstnumber(K54)
" and get the value !

The difference is just
Public Function GetFirstNumber(strText As String)
and this at the last row of the function: GetFirstNumber =
CSng(strText)

HTH

Chris

R

#### Rick Rothstein \(MVP - VB\)

Here is my offering...

Function ExtractNumber(rCell As Range) As Double
Dim X As Long
For X = 1 To Len(rCell.Value)
If Mid\$(rCell.Value, X, 1) Like "*[0-9.]" Then
ExtractNumber = Val(Mid\$(rCell.Value, X))
Exit For
End If
Next
End Function

Rick

C

#### caroline

Hi,
This is brilliant thanks.
Is there anyway I can also select decimals
like in 1.5 in ft1.5drt?
If c Like "#" or "." Then
but it did not work
Thanks again

R

#### Rick Rothstein \(MVP - VB\)

I guess I should mention... if the number can contain a decimal point and
the region settings are for the decimal point to be a comma, or if the
number contains "thousands separators", then the function, as posted, won't
work. This one will for either decimal point setting (provided the number
still doesn't have thousands separators in it)...

Function ExtractNumber(rCell As Range) As Double
Dim X As Long
For X = 1 To Len(rCell.Value)
If Mid\$(rCell.Value, X, 1) Like "*[0-9.]" Then
ExtractNumber = Val(Replace(Mid\$(rCell.Value, X), ",", "."))
Exit For
End If
Next
End Function

Rick

Rick Rothstein (MVP - VB) said:
Here is my offering...

Function ExtractNumber(rCell As Range) As Double
Dim X As Long
For X = 1 To Len(rCell.Value)
If Mid\$(rCell.Value, X, 1) Like "*[0-9.]" Then
ExtractNumber = Val(Mid\$(rCell.Value, X))
Exit For
End If
Next
End Function

Rick

caroline said:
hello,
I am using the following code to extract numbers from a string
http://www.ozgrid.com/VBA/ExtractNum.htm
however when I have strings like WS123ABC45cft, I would like to extract
only
the first set of number"123". The code provided extracts "12345"
Do you know how to correct the code?
Please note that the number of numbers and letters are variables so I
cannot
use LEFT or RIGHT.
thanks

G

#### Gary''s Student

You were very, very close:

Function numit(r As Range) As Double
Dim s As String, s2 As String, c As String
Dim b As Boolean
b = False
s2 = ""
s = r.Value
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c Like "#" Or c = "." Then
s2 = s2 & c
b = True
Else
If b = True Then Exit For
End If
Next
numit = --s2
End Function

R

#### Rick Rothstein \(MVP - VB\)

If c Like "#" Or c = "." Then

Or... If c Like "[0-9.]" Then

Rick

G