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

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

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

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

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

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

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

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

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

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

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

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

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

  13. i like it

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

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

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

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

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

  19. hello

    how to format a cell like: AAAA12345-6?

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

  21. 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]###"."####;###"."###"."####

  22. Super useful. thanks

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

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

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

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

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

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

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

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

  31. Great post, there is a way to create a custom format to change numbers for letters? For example in a sheet with test results, if I type 90 or more replace the number with "A" if I type a number from 80 to 89 its returns "B" instead of 80. I already have something similar with conditional formatting @" B" and the result is for example "85 B" or "90 A", There is a way to hide this "85" and "90"?

  32. Hi,

    How do I display this number "SUSU/067/2018" in Arabic? I've tried using "[$-ar-SA,200]0" but it doesn't work.

  33. hi. if you have several rows where some rows have the same text content while others have different, how do you NOT repeat showing the row content with same value?

    for example, rows 1 to 5 have ABC value and rows 6 to 10 have DEF value. how to show only ABC in row 1 and blank in remainings rows until it hits a row with a different value like DEF in which case, it should show the first occurence of DEF and blank for succeeding occurence.

    i have a picture to make it clearer but how to attach here?

  34. Hi,
    I have cells that contain both text and numbers such as ABC 123. Within the same column, I also have ABC 23. When sorting, 123 comes before 23.
    Is there a formula to make ABC 23 ABC 023?
    Thanks.

  35. how can i write in excel in one cell 55-10-a with data entry input 5510a
    eg: 60-10-b with data entry input 6010b, means last one is letter and others are numbers.
    when i gave in cell format 00"-"00"-0 its working only for numbers...if i write 55101 wil display as 55-10-1 like this...but if i give 5510a it will display same as 5510a
    if any solution for this pls help me
    thank you

  36. I have a dilemma that I can't resolve and can find no relevant examples.
    I have two tables with Class mnemonics listed in the format - 00:00.00.
    some are formatted as custom but with different custom formats and some are as general.
    some show up as 00:00 while others might be 00:00:00.
    The last 2 digits may sometimes be intentionally blank which adds to the confusion.
    I need to get all these into a consistent format so I can do a VLOOKUP across the 2 tables and match data.
    Also, if I try formatting to test or number I get very strange results and sometimes the format wants to make it time (complete with am or pm) aaaggghhh.
    for example 28:08.92 sometimes shows up as 28:08.9. 28:08 may show up as 28:08 or 28:08:00. If it is formated as general it is 0.019548, if it is number it is 0.06, there is a time format of 28:08.9 but if I try to make a custom I get a message that says Microsoft cant use the number format I typed.
    Any thoughts?

  37. Great job! Thank you.

  38. Hi,
    How do I format a decimal number to the unit using the costume format.
    I don´t want it to roud the number.
    Example: The number is 3,84 and I want it to display only the number 3 (not rouning to 4)
    Thank you

    1. Jose:
      You can use the ROUNDDOWN function to return only the first digit like this:
      =ROUNDDOWN(M40,0)
      Where 3,84 is in cell M40 you want the function to return only the first digit.

  39. hi
    I Have a Question About An Access File?
    Who can help?

  40. Maybe the code is something like
    "B"0000/00/
    But the problem is that excel doesn't let me to change/enter the code in the costume format cell box

    1. I don't think its a matter of formatting the cell. As best that I can tell you can achieve the end result like this. Because there is no pattern to the text other than the number of digits I used three different formulas.
      For the seven digit texts I got this to work:
      ="B0"&LEFT(O1,3)&"/"&MID(O1,4,2)&"/"&RIGHT(O1,1)
      For the eight digit texts this works:
      ="B"&LEFT(O2,4)&"/"&MID(O2,3,2)&"/"&RIGHT(O2,2)
      For the nine digit texts this works:
      ="B0"&LEFT(O3,3)&"/"&MID(O3,4,2)&"/"&RIGHT(O3,3)
      I use Excel 2016, so my concatenation function might be different than yours.

    2. Hi,Please send sample file.

  41. Hi,
    I enter seven,eight,or nine digits as text in a cell and I want it to be formatted as follows,Pls. help me what is the custom format code
    For example:
    0348971>>>B0348/97/1
    23209715>>>B2320/97/15
    044499565>>>B0444/98/565
    What ever format I choose to change the code the excel returns back an error msgbox that ((not included,excel can't create...)).
    Pls help me with the case which format code I should choose or write in the costume format...

  42. hi
    I have a problem with Access. Can anyone help?

  43. Hi,
    that format is custom "0000" and "AMN-1" Format Is General(
    Of course he did not care),The two cell formats are the same. when click on "0001" cell shown "1" I do not know what to do. Can i send you a file?
    thanks

  44. hi
    i have AMN and 0001 IN tow column in excel ,when CONCATENATE(A1,"-",B1) It shown AMN-1 ,and do not show 000.
    why?
    what happen this problem? and what's the solution? pleas

    1. Habib:
      Is the "0001" in B1 formatted as text or a custom format "0000"?
      How is the cell that displays "AMN-1" formatted?
      It appears as if the formatting is different for the two cells.
      Excel won't display leading zeros as in "0001" unless the value is text or the cell has the Custom Format "0000". The General formatting will work for this, too.
      In your case I would assume that General formatting will work because there is the "AMN-" included with the "0001".
      Right click on the cell that holds the "0001" and choose Format Cells, then choose General from the list. Do the same for the cell that holds the concatenated result. Both cells should be formatted as General.

      1. Hi,
        that format is custom "0000" and "AMN-1" Format Is General(
        Of course he did not care),The two cell formats are the same. when click on "0001" cell shown "1" I do not know what to do. Can i send you a file?
        thanks

  45. Awesome document: clear, to the point, and the instructions work! Glad I found this website.

  46. Hi.. i want to convert following numbers

    19,50,000 to 19.50
    01,00,000 to 1.00

    1. Vasu:
      I think what you want to do is display the numbers with two decimal places.
      You do this by formatting the cells that contain the numbers.
      Start by selecting the cells that contain these numbers then right click, choose Format Cells, then click the Numbers tab and you will see a list of formatting options. You can test each format for your particular need.
      If I'm understanding your question, you want the Number format as its default display uses two decimal places.

  47. Hello,

    How can i write in excel "5-15" whenever i write it shows May 15,

    Please help.

    1. Hi Sandeep,

      Set the cell's format to Text, and then type 5-15.

  48. I need to format numbers in excel as below:

    100,00,00,000/-

    How to do?
    Please..

  49. Hi,
    How can I make a '0' on the left side of another digit visible in the cell? Ex. 03, 01, etc.
    Thanks, Carolina

    1. Carolina:
      Change the number format to custom to include the number of zeros and digits you need e.g., "0000". This will display 0123.
      Select the cells you want to format right click the cell
      selection
      Select Format Cells
      Select the Number tab
      Select the Custom option
      Highlight the first 0 option
      In the Type field enter the total number of digits you want to display. For example, entering five zeros will allow the cells to display five digits including any leading zeros.
      Click OK and you're ready.

  50. i wonder in the customized number format which is 0;;;@
    how could i show the cell with the negative values with pharenthesis?
    just wondering why? for my excel formatting project,
    thank you so much

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