Is there a quick way to pick out the numeric values from string.

[neemi]

I have an ID field which for some reason on some of them people have added text to it...

e.g. 12345BD, 34567/other, etc

Is there a quick way I can do something in a query to just pick out the numeric of the field

so the above would be...

e.g. 12345, 34567

Cheers,
Neemi

 

[CosmoKramer]

Neemi,

Is the numeric part always at the beginning?? If so, try the Val function:

Val([FieldName])

Si hoc legere scis, nimis eruditionis habes

 

[gol4]

unless someone else has easier
use a function
place in a module

Function getNumbers(source) As String
    Dim tmp As String, i As Integer
        tmp = ""
    For i = 1 To Len(source)
        If IsNumeric(Mid$(source, i, 1)) Then
            tmp = tmp & Mid$(source, i, 1)
        End If
    Next
       getNumbers = tmp
    
End Function

then from a query

=getnumbers([fieldname])

 

[PHV]

Has you want to preserve the spaces and commas:

Public Function getNum(myField)
  Dim s As String, l As Long, x As String
  If Trim(myField & "") = "" Then
    getNum = myField
    Exit Function
  End If
  For l = 1 To Len(myField)
    x = Mid(myField, l, 1)
    If IsNumeric(x) Or x = " " Or x = "," Then
      s = s & x
    End If
  Next l
  getNum = s
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886