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 12. Total comments: 643

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  24. 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_ _ ;@

  25. 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:"

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

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

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

  29. 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 :-)

  30. #.##% = 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.

  31. Shaina:
    Your request is somewhat confusing because Excel is not going to see "L18-021547" as a number. Then you say you're trying to come up with a way to see the field as a number with an asterisk at the beginning and end of the text.
    =CONCATENATE("*",A2,"*") will add an asterisk to the front-end and back-end but it will still be text. Excel is not able to convert the letter "L" and the "-" into a number.
    Why do you need the data as a number?

  32. We get a list of numbers in that actually start with a letter (Example L18-021547) and I would like to figure out how to apply a format to those numbers that adds an asterisk (*) to the beginning and the end of that without having to add them to every single field manually (*L18-021547*). We get a bundle of 100+ of these numbers for each report and I am just trying to come up with a way for it to see the field as a number, which it is not doing at the moment because it starts with "L". Any suggests?

    1. try using this formating in cell format

      "*L"00-000000"*"

      each time you just need to type out the number and it will add the * and also the L for you.

  33. i like it

  34. Hi,
    could you please help me with the date format, I want space between days and months numbers like dd/mm/yyyy to d d/m m/yyyy.(2 2/1 1/2018)

    Thanks

  35. I want convert this text 20122018(ddmmyyyy) to this date dd-mm-yyyy format please how we will convert using text formula

  36. If I am trying to format a phone number to read (so zeros need to be visible):
    (XXX) XXX XXXX
    and
    1 (XXX) XXX XXXX

    I cannot find the code to use a comma...

  37. Hi Svetlana,

    Trying to use Custom Format to color numbers in a cell based on the following conditions: green for numbers less than or equal to 45, amber for numbers greater than 45 but less than 60, and red for numbers 60 and greater.

    Any suggestions?

  38. Hi Svetlana,

    Trying to use Custom Format to color numbers in a cell based on the these conditions: green for 45 but =60.

    Any suggestions?

  39. hello

    how to format a cell like: AAAA12345-6?

  40. I NEED TO CONVERT ALL THE NUMBERS THAT I ENTER SHOULD BE IN LAKHS.

  41. I am trying to format my phone numbers to look like this: 303.555.9876, but instead they look like this:3035559876..

    I will like to know how to fix this problem.

    1. This works for me.
      [<=9999999]###"."####;###"."###"."####

  42. Super useful. thanks

  43. I need to convert a 00000 type formatted cell content (shows as 00123) to text with same number of leading zeros. Since Excel understands the value only as 123 converting the cell format to text removes the zeros. Thanks in advance.

  44. I'm trying to round the number 1,230 to 1,200; or 43,540 to 43,500. Thanks.

  45. Hi Svetlana,

    I'm having trouble formatting text and would like to omit the first two letters. I'm aware that I could use RIGHT(CELL,LEN(CELL)-2) but I don't want to change the actual text, just to display it in a different way, with the underlying 'value' of the cell staying the same.

    For example, I can type in the custom formatting bar "Rob" to display only "Rob" no matter what I actually type into the cell.
    I would like to format cells so that the first two letters aren't displayed, for example, entering "Steve" would show "eve" in the cell, or "Howard" showing "ward". (Sounds weird but I do genuinely need this.)

    Is this possible?

    Thank you for the fantastic article, it was very helpful and well written.

    Rob

  46. I NEED 4556 A,4557 B,4558 C,4559 D ,,,,,,,,,, serial numbers with 6 rows and 6 columns formula send me plz

    1. instead of using conditional formatting in excel you can use a simple formula . 1&char(64+Rows(a$1:a1)) will give you 1A try this

  47. I'm trying to convert a number, say 123.50 to 000012350 (always 10 characters, need pennies but no decimal point.

    Thanks

    1. Hi Kevin,

      Assuming the original number is in A1, the conversion can be performed with this formula:
      =TEXT(A1*100, "0000000000")

      However, the result of the formula will be a numeric string, not a number.

      If the result should be number, you can multiply the original numbers by 100 to get rid of the decimal point (=A1*100), replace formulas with values if needed (an intermediate result will be 12350), and then apply this custom format: 0000000000 to always display 10 characters with the required number of leading zeros.

  48. I am trying to get a number w/ comma separator for thousands but I want the decimal to be automatic. e.g. 1,200 or 1,232.32. But, I can't get the decimal to go away for whole numbers. I get "1,200." Any idea how to do this?

  49. In the default Indian numeric system, I am getting 3,94,97,52,681 from
    _ * #,##0_ ;_ * -#,##0_ ;_ * "-"??_ ;_ @_ .

    how do i get only 3 commas, i.e. 394,97,52,681 ?

  50. Hi, nice page. But I have a problem not mentioned here. :)
    I want to see as many decimal numbers as there are no zeros. I use format 0,???. Works nicely. But in case there are no decimal numbers, result is shown with decimal point (comma) at the end and it looks terrible (ex. "3," instead of just "3"). Is it possible to solve it somehow easy?

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 :)