Formatting numbers in excel – few tips

Reader Nikhil Shah asks in an email:

Dear Chandoo,

I have some Error In Excel sheet.

Problem :-

I have a locker number 01234 567890, in Excel it will be displayed as 01234 567890 with the space. However if it was entered as 01234567890, Excel will display it as 1234567890, without the leading zero. If the data has been read in from a text fill in could be formatted as text and still show 01234567890.

Now I want my locker number see with zero in excel sheet,How it possible,please help me with example.

You can force excel to format numbers the way you want using “format cell” feature. Just select the cell with number you want to format and hit ctrl+1 (or right mouse click > format cells) and go to Number tab in the dialog box. Select “custom” category and enter format as 000000 00000 (6 zeros followed by a space and then five more zeros) as shown below. Hit ok to set the format to display locker number as you desire.

Also, try these things:

• To set social security number (SSN) format, enter 000-00-0000
• To set phone number format, enter 000-000-0000
• To set phone number with 1 digit country code format, enter (+0) 000 000 0000, for 2 digit country codes you can try (+00) 000 000 0000.
• To set social security number (SSN) format, enter 000-00-0000
• To set ZIP code format, enter 00000, for 9 digit ZIP codes you can try 00000-0000

Do read Using Custom Cell Formats in Excel – Tips & Tricks to findout how to format dates, currencies, special formats etc.

| More

Posts & Navigation Have an Excel Question? « Incredible hulk strikes IMDB – awesome online ad Here is a simple way to get Rs. 70 Lakhs when you are 55 – DONT touch your PF » Go Back to Home Page Tags: formatting, howto, learn, Learn Excel, microsoft, spreadsheet, technology, tips, tricks




Trackbacks & Pingbacks
• Pingback by Formatting numbers in excel - few tips - Learn Excel on June 24, 2008 @ 1:25 am
• Pingback by 100 Excel Tips & Resources for Everyone | All Time Hits | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on May 30, 2009 @ 2:10 pm



Comments

Kidakaka June 18, 2008


Smart tip PHD

Ahmad Tarawneh March 15, 2010


Dear Sir,

I have to write numbers in Excel as follow 1.11205080 but unfortunately I’m losing the last digit Zero, and if the cell format was changed to text to keep the last digit (Zero) i can’t find the needed number by using the control + F, and entering the the full number 1.11205080 now way to find it, but if I entered the number without the the last digit I can find it by using the control + F please advice.

Best Regards

Ahmad T.

Avinash Beepath March 18, 2010


Ahmad,
To create a generic custom format that would work for your problem above.
1- Go into format cell (ctrl + 1)
2-Custom cells
3-enter ‘0.00000000′ without the quotes (this can be modified to your specifications of digits before decimal and after).
Hope this helps..

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL

Notify me of when new comments are posted via e-mail




If you have a question, please ask in the forums