extract numbers from string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gina - 31 Mar 2005 17:07 GMT

Hi.

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

thanks,
Gina

Reply to this Message

Dirk Goldgar - 31 Mar 2005 17:27 GMT

> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Gina - 31 Mar 2005 17:41 GMT

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.
> >
[quoted text clipped - 11 lines]
> Give a full set of examples, and we may be able to suggest the best
> method.

Reply to this Message

Dirk Goldgar - 31 Mar 2005 17:56 GMT

> Dirk
> thanks for your very fast answer!
[quoted text clipped - 3 lines]
>
> 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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Gina - 31 Mar 2005 18:05 GMT

Thanks, Dirk ...

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

Regards,
Gina

> > Dirk
> > thanks for your very fast answer!
[quoted text clipped - 18 lines]
> ?Val("Rotate Tires")
> 0

Reply to this Message

Dan Artuso - 31 Mar 2005 17:50 GMT

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

Signature

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

> Hi.
>
[quoted text clipped - 4 lines]
> thanks,
> Gina

Reply to this Message

Gina - 31 Mar 2005 18:03 GMT

Thanks a lot, Dan ....

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

Gina :)

> Hi,
> Here's a generic function that simply returns all numbers within a string:
[quoted text clipped - 27 lines]
> > thanks,
> > Gina

Reply to this Message