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

  1. Hi

    Thanks for the page.As you said,holding ALT key and pressing ANSI code, say 0176 for degree symbol returns a error sound.any other method for format numbers with special characters? i'm using office 2010 version.

    1. Make sure you are typing on the numpad. And make sure the numlock is on.

  2. I have a number 1.026596 but I'm wanting to format it to read 1026.596 so what Custom format would I use??? Thanks!

    1. Hi Phil,

      I am unable to find any format to change decimal place however you can use below formula.

      Imagine Your value is in A1
      so paste this formula in A2 Cell.

      = CONCATENATE(LEFT(A1,1),(MID(A1,3,3)&"."&(MID(A1,6,10))))

      1. There are a few issues I'd have with the suggested formula:

        * What happens if the length of the whole number portion is longer? This formula depends upon the decimal being the second character.

        * This formula also converts the value to text. If you want to use the value elsewhere, then you have to convert it back to a number.

        Just multiply the A1 value by 1000 and the decimal place will be moved.

  3. What about if I want to a format code to do the following:

    7, 0
    (As many countries use commas instead of decimal points and even have a space following the comma)

    Thanks!

    1. File > Options > Advanced > Editing options > Use system operators > select "," as the Decimal separator

  4. How do I get the result of a formula to display as currency?
    I have already formatted the cells as currency, they are all the same.

    This is my formula. I am starting with a base number, not in a cell.
    =IMSUB(390,H19)

    The number in cell H19 is a currency amount, and formatted as such.
    my result is 240, but it will not show up with the '$' no matter what I try.

    1. ;;;$@

  5. hi, i want to change the thousand separator for Style 1,234,633.53. when I have tried as per the suggestions above..I can only change as 12,34,633.53.

    Please help me out..

    thank you..

    1. HOW TO CHANGE THE NUMBER FORMAT AS 54,64,789.25

  6. Great job making these instructions! I easily found what I needed for a change! Kudos.

  7. hi,

    when i am trying to make a histogram out of my data , in x- axis it is showing two values with a comma in between , i want to replace dash(-) in place of comma(,)

    can anyone help?

    thanks in advance

  8. Hi

    if we enter in cell value as a 88 55 68 means Lenght width height should show in cell L88xW88xH68 any formula is there? Please help me

    1. If they are in cells A1 B1 and C1 then ="L"&A1&"XW"&B1&"XH"&C1

  9. How can format a cells to read same digit,I be an excel sheet with numbers in four digit,now am crossing to 5 digit and keeps rearranging it bring eg 10000 before 1001

  10. Hello all,

    I want to create an Excel sheet with user names and Passwords which can be opened by multiple users. However, I do not want them to see the passwords entered there.

    Example:

    User ID: User 1
    Password: AB12

    Instead of seeing AB12 in the cell, I would like to see XXXXXX or ****.

    Is that possible?

    1. Reconsider storing usernames and passwords in Excel.

      Otherwise, just type
      ;;;XXXXXX
      into the format field.

  11. How to write in excel cell like password in any website login.
    Want to write : 123456789
    Look like : *********
    Reply if any formatting available for it or any function.

    Thanks in advance.

    1. Reconsider storing usernames and passwords in Excel.

      Otherwise, just type
      ;;;*********
      into the format field.

  12. All of my excel spreadsheets seem to be corrupted. When I go to the format cell, the only option given me is negative numbers in Numbers and Currency. What happened and how do I get back to the default Format?

  13. how to place thousand separator as follows:
    the number is 19 lacs, 05 thousand, six hundred fortyfive and zero paisa

    that should be 19,05,645.00

    thankyou

    1. HOW TO CHANGE NUMBER FORMAT AS 12,54,780.34

  14. Hi

    I have a column of about 2000 numbers. How do I change the number format from, say, 11709 to 1170/900?

    Thanks

  15. Hi dear Svetlana,

    some times when I writ number in a cell, the number automatically changes to date.
    how can I solve this problem.please help me.
    thanks

    1. right click the cell and go to number format
      choose general

  16. sir, want to display A = 22° 05' 17.10 with increase A,B,C,D......
    i use the custom formula is #° 00' 00.00 for 22° 05' 17.10.

    please help me

  17. Hi,

    Hi How can I enter format like x.xxx-xxx.x (digits only)
    For example--------------------1.234-567.8
    in excel.

    plz guide me on the above.

    1. 0"."000"-"000"."0

  18. I have a mixture of digits and numbers and would like to format cell to have leading zeros
    eg. 12365L to show automatically as 00012365L

    1. Press CTRL+F or Ctrl+P, then go to
      number, search Custom, copy this code

      00000000"L"

    2. I am also seeking a solution for leading zeros with a mix of digits and numbers so I can sort by number, rather than having all text-only entries at the end. For example, I want the following data: 001, 001A, 001R, 002, 002R to sort in that order. If I format as text, my leading zeros disappear and if I format as a number using 000 as a custom format to maintain the leading zeros, the data sorts as follows: 001, 002, 001A, 001R, 002R. Any suggestions would be greatly appreciated!

  19. How do I put commas in the numerator and/or denominator of fractions? For example, 23 3,289/4,536. I tried various combinations of #, 0, and ? but to no avail.
    I tried:
    # #,###/#,###
    ? ?,???/?,???
    # #,##0/#,##0
    # ?,??0/?,??0
    and others.

  20. How do I make it so that:
    Zero = Black
    Less than Zero = Black, in brackets, thousands separator, no decimals
    More than Zero = Red, no brackets, thousands separator, no decimals

    I think I am close but not quite:
    [Red][>0]#,##0;[Black][<0](#,##0)

    1. Use this code, nice to see
      [blue][>0]"J";[red][<0]"L";General
      change the Font type to Wingdings

    2. Add this
      ;General
      So it becomes:
      [Red][>0]#,##0;[Black][<0](#,##0);General

  21. I have to create a custom format for all of these but I just dont understand the book? May someone please help....

    1.3217874955 i need to create a custom format to look like this 321.787.4955
    2. PN-25831 i need to create a custom format to look like this PN-025769
    3.110 dollars and .9 cents i need to create a custom format to look like this 340 dollars and .90 cents
    4. -150 150 loss its red though

  22. This has been helpful but hasn't quite hit the nail on the head for me. I have a cell that is displaying numbers through vlookup, some of the numbers are whole numbers and others have a decimal place. I would like the whole numbers to show as whole numbers without a decimal point. I also would like to show a unit with it. It seems I can do either one or the other but haven't figured out how to get both into one format. Is it possible to do that?

    1. Immediately figured it out, just had to have General "unit" after the semicolon.

  23. I work for an engineering company and the engineers requests can be quit specific. I have searched and searched to find the code to make diagonal stacked fractions that show the numerator as superscript and the denominator as subscript. Can you help with this.

  24. How can I trim only the decimal part from my figure.
    EG: If the # is 1425.50 or 1425.00 I need only the decimal like 50 or 00

    1. =MOD(1435.50,1) will give you the "remainder" of the number as divided by 1 (the argument in the MOD), so you will have .50.

      If you need it to be 50, then it's =MOD(1435.50,1)*100.

    2. Please give me a solution, Thanks in advance.

      1. Svetlana please help..

        1. Hi Sanoj, it's a very simple solution, just need to "input" it into your formula as text formatted with two digits behind the decimal point:

          Option 1 using your original function:
          =IF(IFERROR(FIND(".",TEXT(B129,"0.00")),0)>0,MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,99),"")

          Option 2 which is much shorter:
          =MID(TEXT(B129,"0.00"),FIND(".",TEXT(B129,"0.00"))+1,2)

          These options would only treat up to two digits behind the decimal point and would trim any additional digits.

    3. I did like this but I am not getting the zero value
      =IF(IFERROR(FIND(".",B129:B129),0)>0,MID(B129:B129,FIND(".",B129:B129)+1,99),"")

  25. Is there a way to get Custom Formatting to be ALL CAPS for datetimes?

    For instance, I am using the Custom Format:
    ddd dd mmm yyyy hh:mm:ss AM/PM
    which, for today and now would be:

    Wed 19 Oct 2016 03:00:00 PM

    What I want are my day of the week and month in text and in all caps just like the US DoD has on their documents (although they use 24-hour time):

    WED 19 OCT 2016 03:00:00 PM

    I can't apply the UPPER function which applies to text because Excel stores the date as a datetime code which is a pure number.

    1. I may have found it:

      =UPPER(TEXT(MyCell,"ddd dd mmm yyyy hh:mm:ss AM/PM"))

  26. Hi

    Adavnce Vlookup examples are very fine, but when we are running the same formula at my system, the result get blanks. Why this is happened. I checked all the syntax however it shows blanl

    1. Hey Svetlana,
      i am wondering how can i fixe some of data in a cell and add only the variable data instead of doing as follow,

      1300-FF-02-0001
      1300-FF-02-0002
      1300-FF-02-0003
      1300-FF-02-0004

      1. 1. Highlight cells you want to format
        2. Bring up cell formatting
        3. In "Custom" tab enter:

        "1300-FF-02-"0000

        4. Click enter and voila!

        All you should have to do is input...
        This: To get this:
        1 1300-FF-02-0001
        2 1300-FF-02-0002
        3 1300-FF-02-0003
        4 1300-FF-02-0004
        etc... etc...

        If you want to add the variable data elsewhere in the text string just alter the "constants" in the formatting and enter your zeros or place holders where you want your variable data to be entered.

        For example:

        Formatting: Entry: Result:
        0000"-FF-02-0001" 1 0001-FF-02-0001
        2102 2102-FF-02-0001
        267594 267594-FF-02-0001 etc...

        "1300-"@"-02-0001" Q 1300-Q-02-0001
        FF 1300-FF-02-0001
        HEY 1300-HEY-02-0001 etc...

        "1300-FF-"00"-0001" 7 1300-FF-07-0001
        99 1300-FF-99-0001
        1277 1300-FF-1277-0001 etc...

        Just make sure that when you format the cell for numbers (using 0s) don't enter alphabetical data. And vice versa for text formatting (using @). If your cell is looking for letters and gets numbers or the other way around, the formatting won't work. you'll just see the numbers or letters you input into the cell.

        Hope this helps

    2. Hi Sandip,

      Sorry, it's difficult to say anything without seeing your worksheets. There can be a number of reasons that cannot be detected remotely, e.g. numbers formatted as text. Usually, we publish a sample workbook containing all of the formulas discussed in a post. Have you downloaded that workbook? Do those formulas work on your side?

  27. Ok how can i customized my excel cell as in ms access. for eg. i want to customized my cell A1 in this format cell A1 list of customers (den names of customers pop up as in ms access where u can create macro for such purpose....Help

    1. Convert the data to table by Selecting the data and then
      Insert -> Table

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