Phone Number Formatting

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?

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 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?

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 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?

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 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?

--

Dave Peterson