| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change 1234567890 to 123-456-7890
I am trying to do the opposite for what everyone else has been asking for
help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
Describe "no luck"... what does happen (that is, what do you see in the
cell)? Rick "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
I assume you meant 10 digits?
Select an empty cell, do a custom format of <=9999999]###-####;(###) ###-#### copy the cell, select the range where you want these phone numbers and do editpaste special and select add Now try it -- Regards, Peo Sjoblom "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
Your Custom forma works for me. Is the cell *number* actually text? Try
testing with =ISTEXT(K1) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
Just out of curiosity, are you seeing a bunch of # symbols in the cell? Is
so, try widening the column. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Describe "no luck"... what does happen (that is, what do you see in the cell)? Rick "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
Doh! I bet that was it
-- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... Just out of curiosity, are you seeing a bunch of # symbols in the cell? Is so, try widening the column. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Describe "no luck"... what does happen (that is, what do you see in the cell)? Rick "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
Hi,
Pulling out your hair is an over-rated experience. You have done well to resist. I am using XL2000. The custom format ###-###-#### works for me. What results does it give you? Regards - Dave. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
The format of the cell does not change. It appears the same and the size of
the column is large enough. "Rick Rothstein (MVP - VB)" wrote: Describe "no luck"... what does happen (that is, what do you see in the cell)? Rick "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
That did it. Thank you so much.
"Peo Sjoblom" wrote: I assume you meant 10 digits? Select an empty cell, do a custom format of <=9999999]###-####;(###) ###-#### copy the cell, select the range where you want these phone numbers and do editpaste special and select add Now try it -- Regards, Peo Sjoblom "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change 1234567890 to 123-456-7890
That is because changing the format does not chnge what is in the cell so it
just remained as text. Peo's solution worked because it forced the contents of the cell to change to a number. But I'm glad that you got it working, that is the important thing. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "ann" wrote in message ... I used your formula and determined it is indeed text. Using Format/Cells I changed it to a number, hit ok, re-opened Format/Cells and tried to change it to a ###-###-#### format. Nothing changed in the cell. "Sandy Mann" wrote: Your Custom forma works for me. Is the cell *number* actually text? Try testing with =ISTEXT(K1) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "ann" wrote in message ... I am trying to do the opposite for what everyone else has been asking for help in resolving a phone number issue. I want to key in each cell nine digits and have it automatically format into a phone number format as shown in my subject. I have gone to Format/Cells/Number to select the Special Type for Phone numbers, no luck. The won't change. Then I thought I would create a custom format of both ###-###-#### and (###)###-####, once again no luck. I have tried everything short of pulling out my hair. Does any one have any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel bar chart formatting of bars to change colors as data change | Excel Discussion (Misc queries) | |||
| Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
| making copied cells change with change in original cell | Excel Worksheet Functions | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
| insert a space in a phone #. change (123)456-7890 to (123) 456-78 | Excel Worksheet Functions | |||
Linear Mode
