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 (555) 555-1212 in my query results.

Thanks,
P.

[lyle fairfield]

Re: telephone formatting

You could put these functions into a Standard Module:

Dim RegularExpressi on As Object

Private Function InitializeRegul arExpression()
Set RegularExpressi on = CreateObject("V BScript.RegExp" )
With RegularExpressi on
.Global = True
.IgnoreCase = True
End With
End Function

Public Function NumericOnly$(By Val vInput As String)
If RegularExpressi on Is Nothing Then InitializeRegul arExpression
With RegularExpressi on
.Pattern = "[^0-9]"
NumericOnly = .Replace(vInput , "")
End With
End Function

Public Function FormattedTeleph oneNumber$(ByVa l vInput)
FormattedTeleph oneNumber = Format(NumericO nly(vInput), "\(000\)\
000\-0000")
End Function

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



On May 17, 8:44 am, "Pat_RI" <pat2469...@zzc ox.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.

[KC-Mass]

Re: telephone formatting

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" <pat2469xxx@zzc ox.netwrote in message
news:xqAXj.2214 $yP7.1594@newsf e18.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.
>

[fredg]

Re: telephone formatting

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(Replac e(Replace(Repla ce(Replace([OldPhoneNumbers],"/",""),".","")," \",""),"-",""),"(","")," )",""),"(@@@ )
@@@-@@@@")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[raskew via AccessMonster.com]

Re: telephone formatting

Hi -

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

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

with fSaveNumer2() being:

Public Function fSaveNumer2(pSt r 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(s trHold, 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).
>NewPhoneNumber s:Format(Replac e(Replace(Repla ce(Replace(Repl ace(Replace([OldPhoneNumbers],"/",""),".","")," \",""),"-",""),"(","")," )",""),"(@@@ )
>@@@-@@@@")

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200805/1