Custom Number Formats

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!

  1. Select the range of cells you want to format in this way.
  2. Right-click the selection and choose Format Cells.
  3. In the Number tab of the Format Cells dialog that appears, select the Custom option in the Category list.
  4. There’s an input box underneath the word “Type:” where you can specify your own custom number format.  In your case, what you want to enter there is 00-000-0000.
  5. Click OK.

The value “268597423” should now appear as “26-859-7423”.

For more information on custom number formats, see this online help article.