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

[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

 

[Gaurav]

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.

 

[Gary''s Student]

="(" & LEFT(A1,3) & ")" & RIGHT(A1,8)

 

[Mill]

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)

 

[Tyro]

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

 

[Rick Rothstein \(MVP - VB\)]

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