extract numbers from string

[Gina]

Hi.

a textbox contains numbers and characters
.... how can I only extract the numbers from that field for some
calculations ?

thanks,
Gina

 

[Dirk Goldgar]

Hi.

a textbox contains numbers and characters
.... how can I only extract the numbers from that field for some
calculations ?

That depends on where the numbers are in the string. If the string
begins with the number, you can use the Val() function to get it. If
it's in the middle of the string or at the end, you have to use the
Mid() or Right() functions, and that's a lot easier if the string is
consistently formatted so that the number is always in the same place
and the same length.

Give a full set of examples, and we may be able to suggest the best
method.

 

[Gina]

Dirk
thanks for your very fast answer!

the number is always at the beginning ... so user may type 5L Oil or 4x
Wheel Changing etc

I need to take out the 5 to do 5 * price

Thanks,
Gina

 

[Dan Artuso]

Hi,
Here's a generic function that simply returns all numbers within a string:

Public Function GetNumbers(strIn As String) As Long
Dim i As Integer
Dim strNum As String

On Error GoTo num_err

For i = 1 To Len(strIn)
If IsNumeric(Mid(strIn, i, 1)) Then
strNum = strNum & Mid(strIn, i, 1)
End If
Next

GetNumbers = CLng(strNum)
Exit Function

num_err:
GetNumbers = -1

End Function

 

[Dirk Goldgar]

Dirk
thanks for your very fast answer!

the number is always at the beginning ... so user may type 5L Oil or
4x Wheel Changing etc

I need to take out the 5 to do 5 * price

If the number is always there, and always at the start of the field's
text, then the Val() function is ideal. It takes everything up to the
first character that can't be interpreted as part of a number:

?Val("5L Oil")
5
?Val("4x Wheel Changing")
4

Be aware, though, that if there is no leading number -- a situation that
*you* might interpret as meaning "1" -- Val() will think it's 0:

?Val("Rotate Tires")
0

 

[Gina]

Thanks a lot, Dan ....

now I have 2 different ways .... something to play around with

Gina

 

[Gina]

Thanks, Dirk ...

now I can decide which one I take .... very nice to have a choice now !!!

Regards,
Gina

 

[themurph2000]

Hi,
Here's a generic function that simply returns all numbers within a string:

Public Function GetNumbers(strIn As String) As Long
Dim i As Integer
Dim strNum As String

On Error GoTo num_err

For i = 1 To Len(strIn)
If IsNumeric(Mid(strIn, i, 1)) Then
strNum = strNum & Mid(strIn, i, 1)
End If
Next

GetNumbers = CLng(strNum)
Exit Function

num_err:
GetNumbers = -1

End Function

--
HTH
-------
Dan Artuso, MVP


"Gina" <[email protected]> wrote in message news:[email protected]...
> Hi.
>
> a textbox contains numbers and characters
> .... how can I only extract the numbers from that field for some
> calculations ?
>
> thanks,
> Gina
>
>

This is what I was looking for. Thanks, Dan.