fbpx
Search
Close this search box.

Formatting numbers in excel – few tips

Share

Facebook
Twitter
LinkedIn

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.

excel-number-custom-formatting

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

36 Responses to “Formatting numbers in excel – few tips”

  1. Kidakaka says:

    Smart tip PHD 🙂

  2. [...] To format a number as SSN, use the custom format code “000-00-0000″… Get Full Tip 47. To format a phone number, use the custom format code “000-000-0000″… Get Full Tip [...]

  3. Ahmad Tarawneh says:

    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.

  4. Avinash Beepath says:

    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..

  5. Steve B says:

    Chandroo,
    What great stuff!
    I need to build a gantt chart template that will automatically fill in the duration blocks when I complete the Start and End dates. I would also like to add an actual End date and have that block filled in in RED.
    Can you help?
    Thanks in advance.
    Steve

  6. Chandoo says:

    @Steve... Thank you so much and welcome to Chandoo.org.

    I suggest you to see this page where you can find more about gantt charts : http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/

  7. PARSHURAM says:

    one cell like 24,25,26,27,28 : i want to direct (-) (maines) automatic funtion require.and this funtion is working than i want selec smone cell 59 one cell -24 is one sell than i want to sum is 35 ans please send me how to solve this problem.

  8. sabya says:

    please help me out,

    how to write 123,456 lacs as 12.34 lacs and 12,345 thousand as 0.12 lacs with the help of number format. in same column / row.

    i dont know vba.

    -- regards

  9. AKSAR HANEEFA says:

    i need the complete notes of Excel please can you help me

    thanking you

  10. John says:

    So simple it's stupid. I want to format a cell so that if I type in 34 it is entered as 3.4

  11. John says:

    first Email address incorrect.

  12. neelam says:

    hi chandoo
    i have  a query i work on a report which comes from oracle it has name and i have use text to column option but my data is not come in same cell.

  13. Augustine Boltz says:

    Below is alternate formatting for the example above.  Obviously, the number of spaces in the 2nd and 3rd examples will not align the numbers correctly unless a decimal is added to the format template for the entered numbers greater than or equal to 1.
     
     
     

    [<1]_($#,##0.00_);_($#,##0_)

    Orig#

    Formatted

    12

     $12

    12.3

     $12

    123.45

     $123

    0.84

     $0.84

    0.075

     $0.08

     
     

    [<1]_($#,##0.00_);_($#,##0"     "_)

    Orig#

    Formatted

    12

     $12     

    12.3

     $12     

    123.45

     $123     

    0.84

     $0.84

    0.075

     $0.08

     
     

    [<1]_($* #,##0.00_);_($* #,##0"     "_)

    Orig#

    Formatted

    12

     $                     12     

    12.3

     $                     12     

    123.45

     $                  123     

    0.84

     $                      0.84

    0.075

     $                      0.08

     
     

    [<1]_($* #,##0.00_);_($* #,##0."     "_)

    Orig#

    Formatted

    12

     $                   12.     

    12.3

     $                   12.     

    123.45

     $                 123.     

    0.84

     $                      0.84

    0.075

     $                      0.08

     

  14. shafeeq says:

    In excel how can i enter my mobile 0509145493

  15. Ania says:

    What's the easies way to get automatically 20.5 when I type 205?
    Thanks
    Ania

  16. GUNA says:

    Its very useful to me

  17. Gabi says:

    Dear Chandoo,
    thanks for the great site. I came across through Google.
    Could you please help me with custom cell formatting in Excel? I need to format a number to have a superscript index, something like this: 2.54^a (I have no superscript here either). If I try 0.00"a" the "a" is not in superscript. Thank you very much in advance.

  18. SP says:

    Need data value in format '123.456.789' converted to format '123456.789' . The acquisition system is putting a decimal point after every 3rd point. Please let me know, if you can help. Thanks!

  19. Nilu says:

    Greate tips....

  20. SATHISH says:

    HI FRIENDS ,

    I WANT TO DISPLAY 27 DIGIT CODE FROM DATABASE EXPORTED CSV - EXCEL ,KINDLY DO THE NEEDFUL

  21. Amy says:

    If I have a program that makes a report, for example people's ID NUMBERS in our office(we have 1 that is 011 and then we have another that is 11 and so on) when our program makes an excel csv file, how do u get the 0 in front of the people's ID numbers? I know how to do it if all the numbers are the same amount of digits but they aren't in this case. Thanks so much!

  22. dheepan says:

    excell sheet phone noumber front side add the 91 noumbers please tell me the steps.

  23. Yash Jain says:

    Hi

    Can you me how to convert old excel file(.xls) format to new file format(.xlsx) without opening them.

    How to do this in bulk quantity.

    • Hui... says:

      @Yash

      There is no need to convert to new formats unless you want to make use of new functions

      There are a number of online or free downloadable convertors available, simple Google excel xls to xlsx convertor

  24. khalid says:

    50581605 0000 0094 694
    50581605 0000 0094 696
    i have these type numbers. i want to convert these into text format with out spaces in the numbers. How i can do.

  25. GHETTOFABULOUS says:

    Dear Chandoo,
    Could you kindly assist. I have data in excel. In column D, I have ID numbers. I want these to be in the format 12-087623K45 (THE NUMBERS AND CHECK LETTER CAN VARY). In other words I want the format to be [TWO NUMBERS, FOLLOWED BY (-) THEN FOLLOWED BY SIX OR SEVEN DIGITS AND THEN A CHECK LETTER OF THE ALPHABET AND FINALLY BY TWO DIGITS.
    However, some of the data is in the forn 12-087623-K-45, OTHERS GAVE ME THE DATA AS 12 087623 K 45. I have so many different formats but my system only accepts the data in the format stated above.

    please assist.
    Thank you

  26. SKG says:

    Hi, dear.
    I want to enter 17-digit no. in excel, either in number or text format, but it should display in format of 00-00-000000-000-0000. How to do it?

    • Hui... says:

      @SKG

      Excel can only accept 15 digit numbers, so unless the first 2 or last 2 numbers are always the same you will have to enter it as text

      As text you have no formatting options
      you can start the text with a ' character and then type the number, but you have to include the -'s
      eg: '12-34-567890-123-4567

      If you don't put the ' character in Excel will try and sole the entry as a Formula
      If you don't want to put the - characters in you will have to use VBA to reformat it after the number
      '12345678901234567 is entered

  27. Dilip says:

    Hi Chandoo,
    I have difficulty getting 1,234,567 on excel. It keeps changing it to 12,34,567.
    I tried this code #,##0 to get 12,345,678 but it does not work (idea from https://www.spreadsheetweb.com/number-formatting-excel/). Can you help?

Leave a Reply