Letters to cut

[S Patel]

Hi
I have a table with lots of records. In one column I have data that has
either number or two letter and number together. I want to cut off the
letters and convert all to numbers. Also some numbers might be starting with
Zero which also I want to remove and stricly make it as a number removing all
those Zeros.
How can I do this?

 

[Larry Linson]

I have a table with lots of records. In one column I have data that has
either number or two letter and number together. I want to cut off the
letters and convert all to numbers. Also some numbers might be starting
with
Zero which also I want to remove and stricly make it as a number removing
all
those Zeros.
How can I do this?

Do you want to convert from a Text Field to a Numeric Integer or Long
Integer Field, or just eliminate the alphabetic characters and lead zeros?
Will there _ever_ be a possibility of alphabetic characters interspersed in
the numeric characters (for example, AB2CD9876)?

Larry Linson
Microsoft Office Access MVP

 

[S Patel]

Hi Larry
Thanks for your response.
No the Alphabets will be just in the begining only and only in few records.
I will be good to convert the number to numerical indiger.

 

[Graham R Seach]

Try this.
Call it in VBA like so, StripEx("sometext123", se_AllButNum)
Call it in SQL like so, StripEx([some field name], 32)

Public Enum StripType
se_Char = &H1
se_Num = &H2
se_NonWord = &H4
se_Space = &H8
se_AllButChar = &H10
se_AllButNum = &H20
se_Custom = &H40
End Enum

Public Function StripEx(sText As String, lExpr As StripType, Optional
sUsrExpr As String = "") As String
' Function to selectively strip any combination of the following from a
string:
' Characters (a-z or A-Z)
' Numbers (0-9)
' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
' Spaces
'-------------------------------------------------------------------------------

Dim objRegEx As Object
Dim sRegExpr As String

Set objRegEx = CreateObject("VBScript.RegExp")
If lExpr And se_Custom Then
'User-defined expression
sRegExpr = sUsrExpr
ElseIf lExpr And se_AllButChar Then
'Strip out everything except alpha characters
sRegExpr = "[^a-zA-Z]"
ElseIf lExpr And se_AllButNum Then
'Strip out everything except numbers
sRegExpr = "[^0-9.-]" '"\D[.]"
Else
'Custom strip
If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\d"
If lExpr And se_NonWord Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)

0, "|", "") & "\W"

If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\s"
End If
'Debug.Print "Regular Expression: " & sRegExpr

With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With

Set objRegEx = Nothing
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia