I have phone number data that has been entered into a column as ##########
with no (###)###-#### formatting. I have used format cellsnumbercustom 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?

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
--
Gary's Student


"Brant Nyman" wrote:

I have phone number data that has been entered into a column as ##########
with no (###)###-#### formatting. I have used format cellsnumbercustom 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?

Exactly what I was looking for! Thanks!

"Gary's Student" wrote:

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
--
Gary's Student


"Brant Nyman" wrote:

I have phone number data that has been entered into a column as ##########
with no (###)###-#### formatting. I have used format cellsnumbercustom 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?

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.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399052

This works great too. Thanks!

"Excel_Geek" wrote:


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.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399052

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

is another alternative.

Brant Nyman wrote:

I have phone number data that has been entered into a column as ##########
with no (###)###-#### formatting. I have used format cellsnumbercustom 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?

--

Dave Peterson