Changing digits in a cell

[Hemming]

I have telephone numbers listed in a column and need to change the area code and the 3 digit exchange. example: 5555555555 to (444)444-5555.
please help

[Carim]

Hi Hemming,

If your data is located in cell A1, this formula :
="("&LEFT(A1,3)&")"&MID(A1,4,3)&"-"&RIGHT(A1,4)

should do the job ...
HTH
Cheers
Carim

[Hemming]

Hi Carim, thanks for the help, it almost worked..... this works great if I only have one cel to change however I have an entire Column how do I change it to change more than one cell ( each cell has a different last 4 digits )

[Hemming]

can anyone help me with this ,,,,it is driving crazy !! thanks

[Don Rylander]

"Hemming" <[email protected]> wrote in
message news:[email protected]...
>
> can anyone help me with this ,,,,it is driving crazy !! thanks
If you have to change them all to the same area code and exchange, I'd use
this formula in another column:

if cell A1 is: 5555555555
put this in cell A2: =value("444444" & right(a1,4))

Then you get a number in A2, which you can format as a Phone Number (using
Format/Cells/Number/Special/Phone Number).

HTH,

Don

>
>
> --
> Hemming
> ------------------------------------------------------------------------
> Hemming's Profile:
> http://www.excelforum.com/member.php...o&userid=32185
> View this thread: http://www.excelforum.com/showthread...hreadid=519308
>

[Hemming]

can this be used in a macro ?????

[Don Rylander]

"Hemming" <[email protected]> wrote in
message news:[email protected]...
>
> can this be used in a macro ?????
It could be, but, for changing the values in a single column, it's simplest
and quickest just to write a formula (using relative cell addressing),
format it properly, and copy it down all the rows.

HTH,

Don

>
>
> --
> Hemming
> ------------------------------------------------------------------------
> Hemming's Profile:
> http://www.excelforum.com/member.php...o&userid=32185
> View this thread: http://www.excelforum.com/showthread...hreadid=519308
>