Convert Phone Numbers

[Guest]

Hello,

I have a directory of phone numbers of the format:

(123) 456-7890
246-8024
135-7913
124-6790
(222) 987-6543
746-3732
264-1234

I need to strip the "-", "(", ")", " ", and assign the [Number] to be the 10
character concatenation of the area code and number. Then I need to store
the record and move on to the next until there are no more records. I did
this in Excel by looking at the previous cell's area code if the current
cell's area code is missing. I do not know how to solve this problem in
Access. So, the result of the above data should be:

[Number]
1234567890
1232468024
1231357913
1231246790
2229876543
2227463732
2222641234

A solution to this will save me hours every month!

Thanks much!

 

[Douglas J. Steele]

The "low tech" approach is:

Replace(Replace(Replace(Replace(PhoneNumber, "(", ""), ")", ""), "-", ""), "
", "")

John Nurick may show up and show you his RegEx (regular expression) approach
if you're lucky!

 

[Guest]

Great, low tech is not so bad, but how can I have a single button that would
read the record, assign the value, and go on to the next record until end of
data?

The "low tech" approach is:

Replace(Replace(Replace(Replace(PhoneNumber, "(", ""), ")", ""), "-", ""), "
", "")

John Nurick may show up and show you his RegEx (regular expression) approach
if you're lucky!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I have a directory of phone numbers of the format:

(123) 456-7890
246-8024
135-7913
124-6790
(222) 987-6543
746-3732
264-1234

I need to strip the "-", "(", ")", " ", and assign the [Number] to be the
10
character concatenation of the area code and number. Then I need to store
the record and move on to the next until there are no more records. I did
this in Excel by looking at the previous cell's area code if the current
cell's area code is missing. I do not know how to solve this problem in
Access. So, the result of the above data should be:

[Number]
1234567890
1232468024
1231357913
1231246790
2229876543
2227463732
2222641234

A solution to this will save me hours every month!

Thanks much!

 

[godiva]

Great, low tech is not so bad, but how can I have a single button that would
read the record, assign the value, and go on to the next record until end of
data?

The "low tech" approach is:

Replace(Replace(Replace(Replace(PhoneNumber, "(", ""), ")", ""), "-", ""), "
", "")

John Nurick may show up and show you his RegEx (regular expression) approach
if you're lucky!

Hello,
I have a directory of phone numbers of the format:
(123) 456-7890
246-8024
135-7913
124-6790
(222) 987-6543
746-3732
264-1234
I need to strip the "-", "(", ")", " ", and assign the [Number] to be the
10
character concatenation of the area code and number. Then I need to store
the record and move on to the next until there are no more records. I did
this in Excel by looking at the previous cell's area code if the current
cell's area code is missing. I do not know how to solve this problem in
Access. So, the result of the above data should be:
[Number]
1234567890
1232468024
1231357913
1231246790
2229876543
2227463732
2222641234
A solution to this will save me hours every month!
Thanks much!- Hide quoted text -

- Show quoted text -

Put it in a query and just run the query once instead of putting it on
a button. Also, change the field so that it does not store the number
formatted.

 

[John Nurick]

I tried to work out how to do it with a query but the requirement to
copy the area code from the previous record seemed to require clumsy
nested subquries. So this seems like a case where a procedural approach
is better. But I have used a regex<g>: the rgxReplace function is at
http://www.j.nurick.dial.pipex.com/Code/index.htm.


Sub FormatPhone347()
Dim rsR As DAO.Recordset
Dim AreaCode As String
'Take a formatted US phone number w 7 or 10 digits
'Remove formatting.
'If 7 digits, prepend the first 3 digits from the most
'recently processed record that had 10 digits.

'ORDER BY clause below must return records in the right
'order for the area codes to be sorted.

'Is error trapping needed to deal with international numbers
'and others that don't have 7 or 10 digits?

Set rsR = CurrentDb.OpenRecordset( _
"SELECT PhoneNumber FROM MyTable " _
& "WHERE PhoneNumber IS NOT NULL " _
& "ORDER BY XXX;")

Do Until rsR.EOF
rsR.Edit
With rsR.Fields(0)
'Get rid of non-numeric characters
'here using rgxReplace (with the Persist argument for speed)
'alternatively use Doug's nested Replace()s
.Value = rgxReplace(.Value, "\D", "", , , , , True)

If Len(.Value) = 10 Then
'capture area code for subsequent records
AreaCode = Left(.Value, 3)
Else
'prepend area code
.Value = AreaCode & .Value
End If
End With
rsR.Update
rsR.MoveNext
Loop

'Tidy up
rsR.Close
'call rgxReplace() with no arguments to dispose of persistent Regex
object
rgxReplace

End Sub