Extracting "numbers" only from phonenumber

[mssbass]

I have a column named "phonenumber". However, some of these are displayed differently than others:

e.g.

(404)888-7777
(404) 888- 7777
404-888-7777
404*888*7777
404(888-7777)

All I want is just the number: 4048887777

Is there a module I can write to update this field?

Thank you

 

[jrbarnett]

Hi mssbass,

Go to the modules tab in your database, click new and copy and paste the following code in it:
Save it as modCleanphones and edit the line indicated to show the name of your table in which the phonenumber field is located. Then, press Ctrl G to open the debug Window and type ? Cleanuppphonenos
- and let it run...

Public Function CleanupPhonenos()
' thread705-583907 By John Barnett (tek-tips handle jrbarnett), 23 June 2003

Dim intCount As Integer
Dim strSource As String
Dim strTarget As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Tablename") ' must change this to name of table which data is stored in

Do While Not rs.EOF ' loop through each record in the table
  strTarget = "" ' reset for each loop
  strSource = rs!Phonenumber ' extract the phoneno field
  For intCount = 1 To Len(strSource)
      ' move everything up
      If IsNumeric(Mid$(strSource, intCount, 1)) Then
        strTarget = strTarget & Mid$(strSource, intCount, 1) ' copy anything that is numeric across to the temp
      End If
  Next
  Debug.Print "Replacing " & strSource & " with " & strTarget ' comment out if you don't want running commentary.
  rs.Edit
  rs!Phonenumber = strTarget ' and save it back in the table
  rs.Update
  rs.MoveNext ' move to next record
Loop
Debug.Print "Finished"

rs.Close
Set db = Nothing
End Function

Hope that this is OK for you. Note that if you are using Access 2000 or XP you will need to go to Tools -> References and add a reference to the Microsoft DAO 3.6 Object library, but this should work fine in 97.


John

 

[mssbass]

I tried pasting and running; however, I'm getting an error that says Compile error: Sub or function not defined. I'm using Access 97.

 

[jrbarnett]

Open the module and go to tools -> compile all modules. If this throws up any errors let me know.
Then press Ctrl G and in the debug window type (it went over the line last time):

? CleanupPhoneNos()

John

 

[mssbass]

Actually what fixed the problem is allowing zero length and duplicates in the table. Thank you for your help

 

[SeeThru]

Here is a very similar one that instead of updating a table, the function returns a phone number. Useful for update queries, and plaves where you do not need the phone number saved anywhere....


Public Function ExtractPhone(StrPhoneInput As Variant) As Variant

'Extracts the digits only from a text field containing a phone number with
'extra spaces, (), -, etc. Use for de-duplicating.

Dim i As Integer
Dim IntLength As Integer
Dim StrBuildString As String
Dim StrOneChar As String

If StrPhoneInput = "" Or IsNull(StrPhoneInput) Then
ExtractPhone = Null
Exit Function
End If

StrBuildString = ""
IntLength = Len(StrPhoneInput)

For i = 1 To IntLength
StrOneChar = Mid(StrPhoneInput, i, 1)
If StrOneChar Like "#" Then
StrBuildString = StrBuildString & StrOneChar
End If
Next i

ExtractPhone = StrBuildString

End Function



HTH

 

[dmwhittaker]

I know that you've probably already fixed this, but a very simple method is the following:

For i = 1 To Len(fn)
If IsNumeric(Mid(fn, i, 1)) Then
fnt = fnt & Mid(fn, i, 1)
If Len(fnt) = 1 And fnt <> &quot;1&quot; Then fnt = &quot;1&quot; & fnt
End If
Next
fn = fnt

Where fn = your original text and fnt ends up being just the numbers.