Remove text from a field

[StratfordWard]

Hello All:

We are trying to update about 8,000 customer records and unfortunately
inherited some fields that heretofore had very few input parameters. We have
fixed that problem but still have some fields, such as phone number fields,
with messy data.

Does anyone know how I can write an expression in an Access Query that will
return all the numeric characters in a field and eliminate all the
non-numeric (a-z, punctuation, spaces, etc.)? It would be ideal if something
like Val() would work on this type of data but Val() doesn't seem to work
here... I guess I could write a really ugly replace(replace(replace...
expression but am wondering if there is a better way.

Here are a few examples of what we have in some of our fields and, of
course, all we want is numbers....i.e. all should become "1234567890"

(123) 456-7890
(123)456-7890
123-456-7890
123.456-7890
123.456.7890
123.456.7890 FAX
Fred Jones-(123) 456-7890
(123) 456-7890 Ext 123
123.456.7890
(123) 456-7890
(123)4567890
(123)456-7890
123.456.7890
123-4567890
Alan Jones 123-456-7890 Fax #
Fred Jones 123-456-7890

In each of the above lines, I would want to extract just "1234567890"
except, of course, where there's an extension number.

Thanks in advance for any ideas!
Stratford

 

[John Spencer]

You could use a VBA function to do this. Here are two functions that
should work. The first will work with nulls, etc. The second will only
work with strings.

IF the returned string is more than 10 characters, you can then modify
the result.

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function

Public Function NumPart(strIn As String) As String
'Faster than fStripToNumbersOnly but it doesn't handle nulls
'or other data types

Dim iPos As Integer
Dim strCh As String

NumPart = ""
For iPos = 1 To Len(strIn)
strCh = Mid(strIn, iPos, 1)
If IsNumeric(strCh) Then
NumPart = NumPart & strCh
End If
Next iPos

End Function

 

[StratfordWard]

John:

Works great - thank you!

BTW, I used the first routine since who knows what folks have put in that
field.

Thanks again!
Stratford