You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
I've decided to start posting some of the replies I send to customers who write me asking for help on how to do something in Excel or Excel Services, especially those which I think might be solutions to problems others are likely to have.
For my first go I've picked - for no particular reason other than it was one of the more recent mails I've gotten - a question that has to do with custom number formats.
This customer writes:
What formula could I use to input a number like this 26-859-7423? with the dashes. I would like to be able to just type 268597423 and the number look like 26-859-7423. Can you help me?
Yes, indeed!
The value "268597423" should now appear as "26-859-7423".
For more information on custom number formats, see this online help article.
Comments: (8) Collapse
Haha some people in my office called an excel expert but guess what I don't know that one. Thanks for sharing :)
Most of the time, you really want to retain all 9 characters. With this format, "012345678" will look like "01-234-5678", but will be stored in the cell as 12345678. To retain the leading zero, you should format these cells as Text.
Hai,
Enter in the Format cells-Custom as "###-##-####" then enter the number 999999999 result comes as 999-99-9999.
This works fine when using dashes(-)but doesn't work when using periods(.) does anybody know how to get this to work with periods in the format instead of dashes.
I am tranferring p/n's from one system to another. Ideally, I would be able to highlight them all in excel and paste them to the new system. The problem is that the current system uses dashes, while the new system doesn't. Some examples of current p/n's are PQ-55-LMJ-U and 3FFN-12-POX-F. The new format will be PQ 55 LMJ U and 3FFN 12 POX F. Is there a formula I can use that will take out the dashes and leave them as empty spaces?
Hi Jon,
Have a look at using the SUBSTITUTE formula. You can use that to replace all the hyphens in a cell with spaces.
Sean.
Hi Stan
Couldn't you retain the leading zero using Joseph's example by preceeding the custom format with "0"? The format "0"00-000-000 should reproduce the format required and retain that leading zero.
Regards
Comments: (loading) Collapse