Extract numbers from string

[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

 

[Gary''s Student]

How about:

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

 

[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

 

[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

 

[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

 

[caroline]

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

 

[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

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

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

 

[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

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Gary''s Student]

Thanks!
--
Gary''s Student - gsnu200776

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

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

Rick