trying to change 000-000-0000 phone format to (000)000-0000 ????

  • Thread starter Thread starter Labtyda
  • Start date Start date
L

Labtyda

This is from a data base that was given to me. I have tried to format the
cells, but it won't change them. Any suggestions?

thanks,
Jean
 
I know there can be better answer than this one. but this one works.

suppose the number is in A1. In B1, write
="("&LEFT(A1,3)&")"&MID(A1,5,3)&RIGHT(A1,5)

drag it down all the way down. once done, COPY the entire column and PASTE
SPECIAL VALUES.
 
Assuming you have the current phone number in cell A2, for instance, the
following could be put into cell B2:
="("&Left(A2,3)&")"&Right(A2,8)
 
You could use Excels special format for phone numbers which is
(000)-000-0000 which will format 1234567890 as (123)-456-7890 or you could
make your own custom format of (000)000-0000 which will format 1234567890 as
(123)456-7890

Tyro
 
Assuming you have a lot of these and want to convert them in place, then you
can use this macro to do that...

Sub ReformatPhonenumbers()
Dim R As Range
For Each R In UsedRange
If R.Value Like "###-###-####" Then
R.Value = "(" & Replace(R.Value, "-", ")", , 1)
End If
Next
End Sub

Anything having 3 digits followed by a dash followed by 3 more digits
followed by another dash followed by 4 digits will be converted. By the way,
if you meant there to be space after the closing parenthesis, then put a
space after the ")" inside of the Replace function.

Rick
 
Back
Top