custom format for phone numbers

Mar 13th, 2002, 07:43 AM

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

kinkyparamour · Mar 13th, 2002, 07:51 AM

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 ]

lenze · Mar 13th, 2002, 07:52 AM

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.

Mark W. · Mar 13th, 2002, 08:05 AM

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.

Mar 13th, 2002, 08:06 AM

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

Mark W. · Mar 13th, 2002, 08:32 AM

Quote:

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.

kinkyparamour · Mar 13th, 2002, 08:35 AM

Mark,
You are right. I stand corrected........Now if I could just figure out the right answer I will feel a lot better.....;o)

lenze · Mar 13th, 2002, 08:42 AM

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.

Mar 13th, 2002, 08:43 AM

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)

Mar 13th, 2002, 08:48 AM

Lenze yours put -- in front of the local numbers and did not put - between the 1 and area code

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 07:43 AM	#1
Guest Posts: n/a	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

Mar 13th, 2002, 07:51 AM	#2
kinkyparamour Board Regular Join Date: Feb 2002 Location: Florida Posts: 82	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 ]

Mar 13th, 2002, 07:52 AM	#3
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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.

Mar 13th, 2002, 08:05 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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.

Mar 13th, 2002, 08:06 AM	#5
Guest Posts: n/a	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

Mar 13th, 2002, 08:35 AM	#7
kinkyparamour Board Regular Join Date: Feb 2002 Location: Florida Posts: 82	Mark, You are right. I stand corrected........Now if I could just figure out the right answer I will feel a lot better.....;o)

Mar 13th, 2002, 08:42 AM	#8
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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.

Mar 13th, 2002, 08:43 AM	#9
Guest Posts: n/a	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)

Mar 13th, 2002, 08:48 AM	#10
Guest Posts: n/a	Lenze yours put -- in front of the local numbers and did not put - between the 1 and area code