extract numbers from string

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

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

thanks,
Gina
 
Gina said:
Hi.

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

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.
 
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
 
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
 
Gina said:
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
Click to expand...

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
 
Thanks, Dirk ...

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

Regards,
Gina
 
Dan Artuso said:
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
>
>
Click to expand...

This is what I was looking for. Thanks, Dan. :thumb:
 
Back
Top