separating numbers from letters

[wdbouk]

Hi,

I have a column where the content is a mix of numbers followd by letters. such as 025816AK (always numbers followd by letters but the length of numbers and letters differ and not always 6 numbers to 2 letters).I am wondering if there is a way to let Access separate the numbers from letters because I am interested only with the observations that have common numbers. is there a way to do so ?

 

[Golom]

The VAL function may work for you

? VAL("025816AK")
 25816

but you will lose the leading zero (if one exists).

 

[TheAceMan1]

How are ya wdbouk . . .

Here's a function that returns the [blue]numeric part[/blue] as a string:

[blue]Public Function NumPart(usrDat As String) As String
   Dim txtNum As String, Idx As Long, NumLen As Long
   
   txtNum = CStr(Val(usrDat))
   Idx = InStr(1, usrDat, txtNum)
   NumLen = Len(txtNum)
   If Idx > 1 Then NumLen = NumLen + Idx - 1
      
    NumPart = Left(usrDat, NumLen)

End Function[/blue]

Calling the function:

[blue]   = NumPart("025816AK")[/blue]

See Ya! . . . . . .