468,103 Members | 1,307 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

telephone formatting

I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?

Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212

I want them to end up all looking like (555) 555-1212 in my query results.

Thanks,
P.
Jun 27 '08 #1
4 1400
You could put these functions into a Standard Module:

Dim RegularExpression As Object

Private Function InitializeRegularExpression()
Set RegularExpression = CreateObject("VBScript.RegExp")
With RegularExpression
.Global = True
.IgnoreCase = True
End With
End Function

Public Function NumericOnly$(ByVal vInput As String)
If RegularExpression Is Nothing Then InitializeRegularExpression
With RegularExpression
.Pattern = "[^0-9]"
NumericOnly = .Replace(vInput, "")
End With
End Function

Public Function FormattedTelephoneNumber$(ByVal vInput)
FormattedTelephoneNumber = Format(NumericOnly(vInput), "\(000\)\
000\-0000")
End Function

You could use them as in:
"SELECT FormattedTelephoneNumber([Business Phone]) FROM Customers"

On May 17, 8:44*am, "Pat_RI" <pat2469...@zzcox.netwrote:
I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?

Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212

I want them to end up all looking like (555) 555-1212 in my query results.

Thanks,
P.
Jun 27 '08 #2
Hi

I assume the slashes, parenthesis, dashes, etc are actually in the text of
the field and that its not so much the
format that you want to change but the but the content.

Look at the replace function in the Access VBA help function.

Create an update query and using the replace function Get rid of all of the
characters other than numerals.

Update to: Replace(TelNo,"-","") 'This gets rid of all the dashes in the
records that have them.
Do the same for the other characters.

You'll be left with 10 digit numeric strings.

If you want to include the actual characters in your data you can do another
update query to put them in.
You can then put in the characters that you want where you want them with
something like:

Update to: "(" & left(TelNo,3) & ") " & mid(TelNo)4,4) & "-" &
right(TelNo,4)
That would yield (781) 678-9807.

Regards
Kevin

"Pat_RI" <pa********@zzcox.netwrote in message
news:xq*****************@newsfe18.lga...
>I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?

Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212

I want them to end up all looking like (555) 555-1212 in my query results.

Thanks,
P.

Jun 27 '08 #3
On Sat, 17 May 2008 08:44:10 -0400, Pat_RI wrote:
I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
() etc is there a way to replace all of these symbols and then reformat
accordingly?

Examples of what needs to be corrected:
555-555-1212
555/555-1212
(555)5551212
555.555.1212
555\555\1212

I want them to end up all looking like (555) 555-1212 in my query results.

Thanks,
P.
Directly in a query (all on one line).
NewPhoneNumbers:Format(Replace(Replace(Replace(Rep lace(Replace(Replace([OldPhoneNumbers],"/",""),".",""),"\",""),"-",""),"(",""),")",""),"(@@@)
@@@-@@@@")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jun 27 '08 #4
Hi -

For A97 dinosaurs (no built-in Replace() function):

? format(fSaveNumer2("( 800 -555/1212"), "(@@@) @@@-@@@@")
(800) 555-1212

with fSaveNumer2() being:

Public Function fSaveNumer2(pStr As String) As String
'purpose: Removes all non-numeric characters from a
' string.
'coded by: raskew
'calls: IsNumeric()
'Input: ? fSaveNumer2("( 800)--555/1212")
'Returns: 8005551212

Dim strHold As String
Dim strKeep As String
Dim intLen As Integer
Dim n As Integer

strHold = Trim(pStr)
intLen = Len(strHold)
For n = 1 To intLen
If IsNumeric(Mid(strHold, n, 1)) Then
strKeep = strKeep & Mid(strHold, n, 1)
End If
Next

fSaveNumer2 = strKeep

End Function

fredg wrote:
>I need to reformat a cell that contains telephone numbers to a format of
(###) ###-#### but the original cells have various symbols such as / . - \
[quoted text clipped - 12 lines]
>Thanks,
P.

Directly in a query (all on one line).
NewPhoneNumbers:Format(Replace(Replace(Replace(Re place(Replace(Replace([OldPhoneNumbers],"/",""),".",""),"\",""),"-",""),"(",""),")",""),"(@@@)
@@@-@@@@")
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200805/1

Jun 27 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ashish | last post: by
3 posts views Thread by Smythe32 | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.