Phone Number Formatting

[Guest]

I have phone number data that has been entered into a column as ##########
with no (###)###-#### formatting. I have used format cells>number>custom and
then entered (###)###-#### to change how the fields are displayed. I'm
trying to find a way to modify the base data so that the formatted brackets
and dash from the phone number appear in the data not just in the field
formatting. I tried pasting values but that didn't work. Any ideas?

 

[Guest]

If your number is in G14, then:

="("&LEFT(G14,3)&")"&MID(G14,4,3)&"-"&RIGHT(G14,4)

will give you a text string as you require. You can then copy the cell with
the formula and paste it elsewhere as value to get a simple string with no
formula

 

[Excel_Geek]

Say the column of your ########## numbers starts in A1. Type this
formula into B1, and copy it down to the bottom of the column:

="("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4)

This will give you (###) ###-####. If you don't want the space, you
can remove the ...&" "& part of the formula.

If you want to get rid of the formulas, copy this new column and paste
special --> values.

Note that this will be text-formatted.

 

[Guest]

Exactly what I was looking for! Thanks!

If your number is in G14, then:

="("&LEFT(G14,3)&")"&MID(G14,4,3)&"-"&RIGHT(G14,4)

will give you a text string as you require. You can then copy the cell with
the formula and paste it elsewhere as value to get a simple string with no
formula

 

[Guest]

This works great too. Thanks!

 

[Dave Peterson]

=text(a1,"(###)###-####")
drag down
and edit|copy, edit|paste special|values

is another alternative.