Selecting numeric values only in a Alphanumeric Textbox

[RezaNana]

hi..
I'm trying to get a numerical values only from a textbox that have an alphanumeric values.It is something like this,
lets say that the alphanumeric value is like this :

SN: T34C-87D66Q (and i just want the numerics only)
which is = 348766
so is it possible? how am i gonna seperate the values. And for your information,some times the positions of the alpha's and characters are not in the same position as in the example i gave above.and i would like to use the Numeric values for calculation..
Please help. Your attention is much appreciated..thanks a bunch

 

[crobg]

That should be accomplished by the following function:

Function NumbersOnly(strValue As String) As String

Dim x As Long

For x = 1 To Len(strValue)
    If IsNumeric(Mid(strValue, x, 1)) = True Then
        NumbersOnly = NumbersOnly & Mid(strValue, x, 1)
    End If
Next

End Function

 

[Eupher]

RezaNana,

crobg has it right, that should work. Note, however, that the function returns a string. If you intend to use the result in mathematical calculations, you'll need to convert the values to a numeric data type (Integer, Long, Double, etc.) - or you could change the function to return a numeric type instead of a string i.e.

Function NumbersOnly(strValue As String) As [COLOR=blue][b]Long[/b][/color]

The downside to this is that if there are no numbers in the input string, the function will return 0 - which may be just fine for your purposes.

HTH,

Ken S.

 

[RezaNana]

I dont really know how to execute this codes. can you elaborate..for your info..i'm just a beginner...

 

[crobg]

Open a new module. Paste the code in. Then in your query you can reference the function like any other built-in function. Your QBE might look like
SNNumbersOnly: NumbersOnly(SN)

SN being the field you want to convert.