Formatting Phone Numbers with Periods

[Guest]

We are doing a data conversion from OpenVMS to Quicbooks by converting to an
Excel comma delimited file and importing to Quicbooks. Our problem: Column O,
P and R are phone numbers, fax numbers and cell phone numbers and are entered
in the cell as 10 digit numbers (Ex: 1234567890) and we need them converted
to 123.456.7890 (not just displayed, but actually in the field.) The columns
have headers in row one and the data values are from row 2 through 1141. I
have tried cut and paste special to a new column using custom formatting
###.###.#### which ends up as ##########.. We cannot figure out how to do
it and have very little experience with Excel formulas. PLEASE HELP!

 

[JMay]

Something like this:

Sub Macro3()
Dim Cell As Range
Dim MyRng As Range
Set MyRng = Range("O21141,R2:R1141")
For Each Cell In MyRng
Cell.Value = Left(Cell, 3) & "." & Mid(Cell, 4, 3) & "." & Right(Cell,
4)
Next Cell
End Sub

HTH,
Jim May

 

[Jim Cone]

If you want a formula...
=MID(D2,1,3)&"."&MID(D2,4,3)&"."&MID(D2,7,99)
This assumes your telephone numbers are in Column D.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Cam" <[email protected]>
wrote in message
We are doing a data conversion from OpenVMS to Quicbooks by converting to an
Excel comma delimited file and importing to Quicbooks. Our problem: Column O,
P and R are phone numbers, fax numbers and cell phone numbers and are entered
in the cell as 10 digit numbers (Ex: 1234567890) and we need them converted
to 123.456.7890 (not just displayed, but actually in the field.) The columns
have headers in row one and the data values are from row 2 through 1141. I
have tried cut and paste special to a new column using custom formatting
###.###.#### which ends up as ##########.. We cannot figure out how to do
it and have very little experience with Excel formulas. PLEASE HELP!

 

[Jim Cone]

http://www.cpearson.com/excel/newposte.htm