Text String Manipulation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a Customer Number stored as text such as: 1003806N200

Is there a function I can use that would remove the alpha character (leaving only the numeric characters), no matter where the alpha character may appear in the string?
 
I've got a Customer Number stored as text such as: 1003806N2001

Is there a function I can use that would remove the alpha character
(leaving only the numeric characters), no matter where the alpha
character may appear in the string?
Click to expand...

If it's always the same "N" you can use the Replace function if you
have access 2000 or later.
Replace([FieldName],"N","")

If the letter can vary, or if there can be more than one letter, i.e.
10025BG236UM89, then, in all versions of Access ....

Paste the following function into a module:

Public Function RemoveAlpha(strIn as String) as String
' To remove all Alpha characters from a string

If IsNull(strIn) Then
Exit Function
End If

Dim intX As Integer
Dim intY As Integer
Dim strOut As String
For intX = 1 To Len(strIn)
intY = Asc(Mid(strIn, intX, 1))
If intY >= 48 And intY <= 57 Then
strOut = strOut & Chr(intY)
End If
Next intX
RemoveAlpha = strOut
End Function
=============

You can call it from a query:
Exp:RemoveAlpha([FieldName])
 
Back
Top