Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to format
I have a colume of phone numbers formatted as follows (123)123-1234. However
I need a formula to make a new colum exchanging the format to 123-123-1234. Is there a way to do this? thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to format
Are they numbers, displayed with a special format, or are they text strings?
-- David Biddulph "Rick" wrote in message ... I have a colume of phone numbers formatted as follows (123)123-1234. However I need a formula to make a new colum exchanging the format to 123-123-1234. Is there a way to do this? thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to format
=SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-")
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rick" wrote in message ... I have a colume of phone numbers formatted as follows (123)123-1234. However I need a formula to make a new colum exchanging the format to 123-123-1234. Is there a way to do this? thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to format
they are numbers.
"Bernard Liengme" wrote: =SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rick" wrote in message ... I have a colume of phone numbers formatted as follows (123)123-1234. However I need a formula to make a new colum exchanging the format to 123-123-1234. Is there a way to do this? thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to format
If they are numbers then you need a format setting not a formula! See
David's reply. I type 1231231234 into cell Called up cell format with CTRL+1; opened the Special group and selected Phone Number You can get the same effect with custom format: [<=9999999]###-####;###-###-#### best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rick" wrote in message ... they are numbers. "Bernard Liengme" wrote: =SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rick" wrote in message ... I have a colume of phone numbers formatted as follows (123)123-1234. However I need a formula to make a new colum exchanging the format to 123-123-1234. Is there a way to do this? thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to format
Fixed Thanks a bunch and Happy NY
"Rick" wrote: they are numbers. "Bernard Liengme" wrote: =SUBSTITUTE(SUBSTITUTE(A1,"(","") ,")","-") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rick" wrote in message ... I have a colume of phone numbers formatted as follows (123)123-1234. However I need a formula to make a new colum exchanging the format to 123-123-1234. Is there a way to do this? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum format in formula bar | Excel Discussion (Misc queries) | |||
Conditional format formula | Excel Discussion (Misc queries) | |||
Conditional Format & Formula | Excel Worksheet Functions | |||
Format\Formula Help | Excel Worksheet Functions | |||
formula based on format | Excel Discussion (Misc queries) |