Comments on: Custom Excel number format

Custom number formatting in Excel is a very powerful tool, and once you learn how to use it property, your options are almost unlimited. This tutorial explains the basics of the Excel number format and provides the detailed guidance to create custom formatting. Continue reading →

Comments page 11. Total comments: 643

  1. I have $471.4M. and $8B
    How do I convert this value so that M and B are replaced with their respective zeros.
    for example $471.4 --> $471400000

  2. How can I make it so that my histogram shows 10K-20K, 20-30K, 30-40K, etc?

  3. Wow this is an extremely informative page, which I will be saving for future reference. Unfortunately, I don't think it answers my current question, which is, can I format the number to be spelled out in letters? E.g, the number 5 is formatted as "five"? Thanks!

  4. Hello, I'm currently using a formula to combine values from several cells into one cell. While doing that, I am hoping that all of the numbers that get merged into the final cells show up to two decimal points (e.g., 1.20), however, even when I custom format the cells using the 0.00 code, my merged number does not show the last decimal 0 value. So it shows as 1.2 instead of 1.20. Is there any way I can fix this? Just in case it might be helpful, the formula that I am currently using the following formula:
    =IF(c9>.05," ", IF( c9<.001,"***" ,IF(c9<0.01,"**",IF(c9<0.05,"*"))))

    Thank you in advance for your help!

    1. Sorry, that was the wrong formula. Below is the correct one!
      =CONCATENATE(C6," (",D6,", ",E6,")","",G6)

  5. Hi
    I wants the word MATHEMATICS in to this formats
    MATHEMATICS
    Separate it each letter in different cells
    M A T H E M A T I C S
    Now
    M S A C T I H T E A M

    Means take first letter from left side
    M
    Then Last letter of the spelling S
    Then Second letter from left side
    Then Second last letter
    Means the first letters of the spelling and last letters of the spelling one by one
    Please help me

  6. Hi Svetlana,
    Could you please help me on this number formatting?
    My figures are all in thousands. I need to input 1 and it displays as 1,000. (or type 10, displays 10,000 ),type 1.5 shows 1,500 type 10.5 shows 10,500 .(So as to save myself typing the zeros). Then when I sum those figures, the total should be 23,000 (total of
    1,000+10,000+1,500+10,500 (and not 23).
    I'd appreciate your help.
    Thank you in advance.

    1. In Alignment in excel go to number, in that select custom, in that delete General option and insert #, and press Ctrl+j and insert %%% and get the result please

  7. How to set the custom format that can be use in all my excel file and not only the file that I added the custom format? Thanks

  8. I want to display 123° 23.5'E, when I type 12323.5E.
    or
    I want to disply 35° 02' N, when I type 3502N.
    I mean any text follow by any number, display as earth point.
    But I cannot use custom number format. Is it possible.

  9. hi
    hope you can help.
    i have a number, eg. 0602.10.00.3.
    however this number has to have 10 digits as in 0602.10.00.03.
    there has to be a zero before the final digit if it is less that 10.
    i hope you can help
    Kathleen

    1. Hi Kathleen,
      You may try this formula =CONCATENATE(LEFT(A1,11),"0",RIGHT(A1,2)). Value in cell A1. Hope this can solve your problem.

  10. how to get 1,11,11,111.11 format?

  11. How to display 0.99 as 0 and not 1
    Or 1.8897656788 as 1 and not 2
    I need the value do i cant use int or trunc or round function. Its only a matter of how to display

  12. how can I show in a cell if I type 1 it shows me 1 no, if I type grater than 1 it should show me 2 nos

  13. Hi,
    I use IF Function in the cell so that it will show me "yes" or "no"
    The thing is that I want if the formula show "yes" I want it in a red color... been trying to format the cells but nothing work... could you tell me what should i do? many thanks

    1. Sorry, no need to answer... I already got the solution, changing the if formula into a code of 1,0 then formatting the cell to translating the code into a text and coloring according to my condition

  14. Hi,

    Looking to create a format to change a 12 number field into something like this
    123456789012
    to
    ABC1234_123_12345

    Cant seem to find if and how I can achieve this.

    Thanks in advance
    Stephen

  15. Hi Svetlana,
    I use Excel to keep track of job numbers for my work. I am using Office 365. Some of these job numbers begin with a 0 and are 14 digits long, but not all begin with 0. I need the 0 in there and have been doing fine with using 0#############, however, when I create a new worksheet, the custom format is no longer in my excel. Is there something I need to do in order to save it permanently so I don't have to keep creating the format each time I create a spreadsheet? I'm not sure if this information helps but the worksheets I create are exported from a database online so it gets saved in HTML format in my downloads folder first then I resave it in documents folder as an xls.

  16. Simply brilliant. I wan completely lost in the excel 'wildcards' and your explanations and examples saved me.
    BIG thank you

  17. Hey,
    I'm working on a currency (euro) formula, but it's for dutch customers. This meaning that instead of it being written like 1,234.56, we use a period as the thousands separator, and a comma for the decimals. What formula could I use for this? I tried >> "€"\ #.##0,00;[Red]"€"\ \-#.##0,00 << but it keeps jumping back to having the comma as a thousands separator, and a period for the decimals.
    Thanks!

  18. Hi,
    How can i use custom number formatting to display 12,34,45,678.00 to 12.34 in cell without dividing the value.
    Please help
    Thanks
    Sharan

  19. Hi,
    How can i use customer number formatting to display 12,34,45,456.00 to 12.34 in cell with dividing the value.
    Please help
    Thanks
    Sharan

  20. Hi,
    0,, "Mbps" will display 10000000 as: 10Gbps
    How do i display:
    0.000001 as 1 usec
    ?
    Thanks

    1. Of course i meant:
      0,, "Mbps" will display 10000000 as: 10Mbps

  21. Sir
    Thanks in advance.
    We need a single space between word in HDFC0000440 like H D F C 0 0 0 0 4 4 0 through cell formatting only. Please adviese.

    Ram Chander

  22. I have a spreadsheet of part numbers that needs separating into blocks. 0123456789 becomes 0 123 456 789. This works for most of the spreadsheet, but a few numbers refuse to convert. I am using the CUSTOM feature like this ==> 0 000 000 000. Some, but not all of the failed results have a letter in them. i.e. 0 123 A56 789. Any thoughts?

  23. I want to use fraction in excel with value 1750/28000 and display as 1750/28000, as number type. Best regards Janko

  24. Hi,
    I need your helps to convert 100 to 10^2.
    Thanks

  25. Hi is there anyway to put a Line break into custom number formats. I want to do this so I can display the Date and time on my graph. What I current get is:

    00:00 Mon 10 Jul 2019

    What I want to display:
    00:00
    Mon 10 Jul 2019

  26. I want to display the tenths place if it is *NOT* zero and I want to *NOT* display the decimal point if the tenths place is zero. How can I do that?

    For example, 20.23 should be displayed as "20.3" but 20.03 should be displayed as "20" without the quotes.

    In other words, I only want the decimal point if and only if a non-zero digit is in the tenths placeholder.

    Any help is appreciated, Michael Carney

  27. How can i format part of text in a single cell that contains a formula result.
    eg. a cell contains formula result like "Iam 25" i want to format 25 as bold

  28. Hello there,
    I am trying to define the following range from Zero to one:
    - Negative numbers become automatically 0.01
    - Zero becomes automatically 0.01
    - Positive numbers >1 become 1
    Could you please give me some hints on how to define the custom format?
    Thanks a lot!

  29. Hi,
    How can i change number format in this way?
    For Eg: Take a let us take a number 5000000. Currently in excel its showing like 5,000,000. I want the "commas" in this way.. like 50,00,000.

  30. Hi,
    I have a few questions maybe you could help;
    1. I prepared a table in excel like a client data form and in the name and surname columns I want the proper case (Joe not JOE). I used Proper formula and data validation but it doesnt work.
    2. How do I avoid duplicate entries automatically? ( I put a full name column that concatenate name and surname data).I want it to check and stop "John Smith" double entries for instance. How is it done with data validation custom formula?
    3.Is there a template way for e-mail entries as per data validation like ________@_________.com/ru/en etc?
    4.I am also having trouble with dependent data validation entries. Suppose there are two types of buildings and first one has 5 floors while the second has 6. I put data validation list for the blocks but for the next column I want it to choose 5 floors for Block A and the 6 floors for Block B but the dependent list isnt working.Please kindly advise.
    5. I used row formula for autonumbering for client id # and I use vlookup for Client name and it is working but if the line is blank it gets the row number for client Id. I mean like 20090001 and 20090003 instead of 20090002. I want it to continue with consecutive numbering. How is it done?
    I kindly ask for your advice.
    Thank you & Best regards

  31. i would like to have a format that is like a Heading sequence:
    1
    1.1
    1.1.1
    1.1.1.1
    thanks

  32. Hi I am trying to format numbers into billions so displaying 4.38 billion instead of 4,378,772,008, I have over 40 numbers I need to do this for. Any help would be appreciated.

    1. Did you figure this out? I'm doing the same Econ assignment

  33. Hi
    I would like to display the currency in cell D10 which is R 1 345 678,93 in say Cell B6 with preceding text "Order Book Value is". If I use ="Order Book Value R"&D10 I get "Orderbook Value is R1345678,93". How do I get the custom formatting to format the value to look like that in D10. I have tried various custom format combinations, but to no avail!
    Thanks

    Thanks

  34. Hi, I need the display to say: 1506.69.6020. So basically, 4 digts. 2 digts . 4 digts. I set it as " ####.##.####" but it doesn't work. can you help? Thanks, Lillian

  35. Hi,
    I want to display number in 1000 seperator e.g. 1000000 as 10,00,000.00 but on my excel it shows 1,000,000.00
    Please help.

  36. Hi Svetlana,
    I try to format percentage and keep a digit, if the digit is 0, remove it.
    I use the Custom as 0.#%;-0.#%; However it will keep dot in there. For example, 24.0% is showing as 24.%
    How do I remove the dot as well?
    Thank you in advance!

  37. When using accounting format, true zeros display as a dash, but numbers that round to zero display as a zero, so visually you see a column showing some zeros as - and others as 0. Is there a format to make them all display as a dash without using rounding to change the numbers?

  38. I am trying to type a number starting with zero. How to type a number starting with zero.

  39. I am trying to have text display in a cell that displays with a leader. The text within the parentheses needs to display in italics. When I apply the custom format for a leader in the cell (@*.), it removes the italics from the text in parentheses.

    An example would be the sentence below, the text "day, week month" would be shown in italics:
    Today is a nice (day, week, month)..........

    Any idea how to solve so I can keep the italics and also the leader

  40. Hi,
    Need your help to convert the cell number format from 1D5F859A to 9A855F1D in excel

  41. hii
    can i do with 2 number in the cell such {1.2,2.3} so change format make like this -> [1.200,2.300]

    thank you

  42. Dear Miss,

    How to get that in the same cell number is negative regardless of numbers of digits the number is in brackets?
    Example -1,23 is (-1,23) -1,0 is (-1) -23,5698 is (-23,5698) 1,23 is 1,23 2,695 is 2,695 all can be in same cell.

    I tried #,###; (-#;###) but this returns (-1,).
    Thank you

  43. Hi,

    I have the following custom code: "3189967-"000 which works fine, however, sometimes I will need the letter "T" to appear after "000" but not all the time. Do you know if this is possible?

  44. Hi, Svetlana!

    Thank you for your article, its great and helpful.
    Please, tell me, is it possible to display -3.5 (for example) as
    (space)(space)(minus)3.5(space)(space)
    I managed only to place (minus) only before (left from) insignificant spaces, like this:
    (minus)(space)(space)3.5(space)(space).

    Thank you!

    1. Hi Dmitry,
      Thank you for your question.

      If we understand your task correctly, the following formula should work for you:
      =CONCAT(" ", -3.5, " ")

      You can learn more about the ways to concatenate values in Excel in this article on our blog.
      Hope this is what you need.

      1. Dmitry, I'm assuming that this is what you're after?
        _ _ #.#_ _ ;_ _ -#.#_ _ ;_ _ 0.0_ _ ;@

        1. Correction, sorry:
          _ _ 0.0_ _ ;_ _ -0.0_ _ ;_ _ 0.0_ _ ;@

  45. I want to display the following custom format in a cell:
    1 487,67€
    10 487,67€
    101 487,67€
    What would be the code I put in "Type:"

  46. Hi - I need to find a way to change the way my data is being displayed in an excel table that I have... where I have an entry of "00024 01" that is formatted as "General" input... and where the blank space is supposed to represent a decimal point ... my question is, how can I get this entry to display simply as the number "24.01" with no leading zeroes and with the decimal point showing??

    (Note: I have thousands of entries and cannot retype the data and need an automated solution.)

  47. I’m trying to make the 5th digit (1.0003) smaller in size. Does anyone know how to do it? Thank you

  48. Hi guys, I need to make a custom currency format same as accounting one, but I need it in ARABIC figures with ARABIC currency symbol "ج.م.". the numbers & symbol should be aligned same as accounting excel format.

    Any way to do please?

    1. Hi, Mohammed,

      Simply select your data, press Ctrl+1, and go to the Accounting category in the dialogue window that appears. You will see a drop-down menu for Symbols. Pick Arabic (Egypt) from the list and hit OK to save changes.

  49. Svetlana, you are a genius! I've been looking everywhere online for the list of font colors that can be used with custom number format" and only found it on your site (It's 'Magenta', not 'Pink', doh!)
    Your explanations are so well articulated that I've bookmarked this page for future reference.
    Thank you very much for you help, and Merry Christmas from New Zealand :-)

  50. #.##% = 1.%
    genaral% = 100%

    can i know how to remove the decimal point? or did i use the wrong code for it?

    i want it to have the expending function like how the general give where if you don't have decimal it don't show but if you have then only it shows.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)