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

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

  2. Super useful. thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. Great job! Thank you.

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

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

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

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

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

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

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

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

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

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

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

    100,00,00,000/-

    How to do?
    Please..

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

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

  31. Wonderful. Great load of information.

    I would like to know if there is a method that when I type Up in my cell, it shows 1, and when I type Down in the cell, it shows 0.

  32. Any way to get rid of the decimal for whole numbers when aligning decimals (for mixed fractional/whole numbers) with something like the 0.?? format? In other words is there a conditional for determining whole numbers?

  33. This should work:
    Enter this into the Format Cell window under the Number/Custom tab 0,,\M You'll still need to enter the number as 1000000 but it will be displayed as 1M and you can use it arithmetic operations as 1000000.

  34. I'm trying to convert from 1000000 to 1 M in office 2016 and none of the methods above are working. anyone else know a solution?

  35. Is there a custom format to always round up or down or does it have to be part of a formula?
    For example...
    Sometimes when rounding up I want a result or copied value of 1.1 to show as 2.
    Other times when rounding down I want a result or copied value of 1.9 to show as 1.

    Thanks in advance!

  36. Hello,

    Wondering if it is possible to format a cell to automatically divide whatever # is entered into the cell by 3.28?

    Thanks!

  37. Hi!

    I would like to know if there is a way to add an inch (") after the values on my dimensions?

    For example:

    2.75x5x.25 is my dimension, and I need it to look like 2.75"x5"x.25" on Excel.
    Is there a way to do that? Any help would be greatly appreciated.

    Thank you!

    1. The way to do this is to enter #?'' in the custom format list and then apply that formatting to the cell. Those are two single apostrophes after the question mark. The cell will display 2.75'', but you can use arithmetical operations on the entries.

  38. I am trying to use Custom format for a chart. If the data is a number that it is pulling from, then I want a number to be shown. If the data changes to a currency, I want the chart to automatically change to currency. Is there a way to do that?

  39. I am trying to use Custom format for a chart. If the data is a number that it is pulling from, then I want a number to be shown. If the data changes to a currency, I want the chart to automatically change to currency. Is there a way to do that?

  40. "For example, 1.25 can be shown as 1 ¼ or 5/5."

    That should be 5/4.

  41. hi i need to convet this numebr
    4184
    2459
    4918
    3164
    5942
    4925
    1120
    6804
    7990
    3300

    in

    41,84
    24,59
    49,18
    31,64
    59,42
    49,25
    11,20
    68,04
    79,90
    33,00
    how to do?

  42. Huh? OK, so let's try splitting it up (note that this commenting system also deleted the ">" in the ">=". So maybe it doesn't like the ">" operator?

    The first part should read: "[=1000000]#,#.##,, \M_)"

    If this doesn't work, I give up.

  43. For whatever reason, it truncated the format formula. Here it is in full:

    [=1000000]#,#.##,, \M_)

  44. The conditional formatting is great. It took a while for me to realize that you can then no longer format negative numbers, zero, and text. Thanks for all of this! I did this: [=1000000]#,###.##,, \M_)

    As examples, it get's you this:

    1,666 = 1.7 K
    166,666 = 166.7 K
    1,666,666 = 1.67 M
    166,666,666 = 166.67 M
    1,666,666,666 = 1,666.67 M

  45. The conditional formatting is great. It took a while for me to realize that you can then no longer format negative numbers, zero, and text. Thanks for all of this! I did this: [=1000000]#,###.##,, \M_)

    Example, it get's you this:

    1,666 = 1.7 K
    166,666 = 166.7 K
    1,666,666 = 1.67 M
    166,666,666 = 166.67 M
    1,666,666,666 = 1,666.67 M

  46. Thank you so much honey.

  47. I'm trying to use text and currency in the same cell using distributed indent. with the currency on the right. how do i get the dollar sign to show up?

    ex. Supplies $20.00

    1. These both work, but #2 is the more elegant solution.

      1. "Supplies $"General

      2. "Supplies "$#,##0

  48. Awesome tutorial. Makes for a great reference guide. Thank you!

  49. Thankyou sir, its very helpful for us..

  50. I would like to have a number formatting that does the following:

    10% would be 10%, not 10.% or 10.0%
    ...but 10.5% would be 10.5%
    ...and 105% would be 10.25
    In other words, I don't want any extra 0's after the decimal point, and if there are no fractions, I don't want the decimal point at all.

    Thank you!

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