custom format for phone numbers

G

Guest

Guest
I want to use a custom format for phone numbers, the one excel has uses () I want to use –
Ex. Numbers entered as 2222222 1232222222 18002222222 should be formatted as 222-2222 123-222-2222 1-800-222-2222. Can this be done by a custom format? I have tried to modified the format for phone numbers but cant get it to work. Thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Go to format cells, custom. Type in ###-#### for the first number. Go Back to custom and type in ###-###-####. Continue this till all your custom numbers are installed.
This message was edited by kinkyparamour on 2002-03-13 06:55
 
Upvote 0
Try something like this:

[>9999999](000)000-0000;000-0000 You may need to modify it to include the 1 prefix, such as [>9999999]1-(000)000-0000;000-0000 wher the user would not enter the leading 1.
 
Upvote 0
A "number" entered as "2222222 1232222222 18002222222" will not be treated as a numeric value by Excel because of the imbedded space characters and; therefore, its display will not be governed by an custom number format. Furthermore, if you exclude the spaces the value will exceeds Excel limit of 15-digit integers.
 
Upvote 0
I need to be able to format the whole column as one format and it will change the numbers as need based on what was entered
 
Upvote 0
On 2002-03-13 07:06, Anonymous wrote:
I need to be able to format the whole column as one format and it will change the numbers as need based on what was entered

See my posting above. You cannot enter these as numeric values. Custom number format can only be applied to numeric values.
 
Upvote 0
Mark,
You are right. I stand corrected........Now if I could just figure out the right answer I will feel a lot better.....;o)
 
Upvote 0
I think I'm confused as to what you have. As I understand it, you have imported a column with phone numbers of various lengths, such as 222222 and 5554446666 and 1555444666. Is that correct or do you have 222222 5554446666 1555444666 in one cell? If you do, Mark is correct, that is not a number format. If it's the first scenario, then use the custom format idea I previously posted. Replace the () with - if you wish.
 
Upvote 0
Sorry for the confusion, they are three different numbers, showing if somebody enters a local number (2222222), area code and number(1232222222), or 1 area code and number(11232222222)
 
Upvote 0
Lenze yours put -- in front of the local numbers and did not put - between the 1 and area code
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top