telephone formatting

[Pat_RI]

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 [phone] in my query results.

Thanks,
P.

[lyle fairfield]

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" <[e-mail]:

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 [phone] in my query results.

Thanks,
P.

[KC-Mass]

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 [phone].

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 [phone] in my query results.

Thanks,
P.

[fredg]

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 [phone] 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

[raskew via AccessMonster.com]

Hi -

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

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

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