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

  1. I have data in IT2019111515691204569 and need to change into text format as

    IT-####-#####-##########

    Please help..

    1. Hello!
      It is impossible to solve your problem by formatting. You can use the formula

      =LEFT(H1,2)&"-"&MID(H1,3,4)&"-"&MID(H1,7,5)&"-"&MID(H1,12,10)

      1. Thanks A Lot.........

  2. I'm trying to add and "*" before and after a number "*"####"*" which works (ie *1234*) but my number has a - in it like 1234-6 when I add the - the * goes away. How do I format this? This is what I'm looking for *1234-5* with no spaces. I don't want to format without the - because the number after the - could be a 2 digit number and the format would only take the last number, like this "*"####-#"*". My number already has the - in it.

    1. Hello!
      You should understand that 1234 is a number and 1234-5 is text. They need to use different formats.
      For text —
      \* @ \*
      or
      \*@\*
      For number - replace the @ symbol with #.
      You can also convert your numbers to text.
      I hope my advice will help you solve your task.

  3. Hi,

    My actual text is "Scheme85" but wanted to appear as "Old Scheme85" without changing actual content of the cell, just for visual purpose...

    Is there any format which can convert my actual text into customized display without changing actual context of the cell?

    1. Hello!
      If I understand your task correctly, you can use custom text format

      "Old" @

      I hope my advice will help you solve your task.

  4. Hi to all,

    I need to use this kind of format for example: (12.5 = 12 + 500 ) it is possible?

    1. Hello!
      You have written a mathematical expression. Moreover, this expression is mathematically incorrect. What does the cell format have to do with it?

      1. We do something similar with surveying and station numbers. For example, a location 13,758 feet from the beginning would be labeled as "13 + 758"
        To make a format like that in Excel, use 00 + 000
        For the question you had above, just write the 12.5 as 12500 and it'll display as 12 + 500
        The biggest issue with what you wrote out is the decimal point. I don't know of any way to make that disappear. You could do 00. + 000 which would display your 12.5 as 12. + 500 That "." is annoying though.

        If you have a long line of numbers formatted as the 12.5, you could put a column that multiplies that column by 1000 and then copy that and paste the value into the original column to overwrite the 12.5 with 12500. (To paste the value hit control + alt + V and select "Values" (or press v) and it'll paste the value rather than the formula)

        Hope it helps!

  5. i have a doubt if i want a variable preceding a constant such as ###/20-2021 in this case ### is a variable like 001 or 002 or ....... 1999 , etc and " /20-2021 " is a contant . when in drag the cell only variable should change.Kindly Help me.

  6. I should have done more experimenting. You can take out the blanks between format conditions. This Code allows for the minus sign up down to Millions:

    [>999999] $#0.0,,,”B”[>99999]$#0.0,,”M” ;[<999999]$-#0.0,,,”B”[<99999]$-#0.0,,”M”

    And it is 81 characters in length. If your number range stays below Billions, then simply change out the 'B' to 'M' and 'M' to 'K' etc. In other words, you can handle two different 1,000 multiple ranges of values with this Format Code structure.

  7. CORRECTION. Excel limits the Format Code to 83 characters, not the number of 85 I posted earlier.

  8. So after some experimenting I discovered how to have multiple formats for Billions, Millions, and Thousands. Here is the solution:
    [>999999] $#0.0,,,”B” [>99999] $#0.0,,”M” [>9999] $#0.0,”K”
    Note that a space is between each format condition (For example, ..."B" space [>99999]... allows for the millions to follow the billions labeling.). By having only this, then all others (negative) will follow the same format. I tried to get the negative but Excel limits the format code to 85 characters. A key piece of information to know.

  9. Hi, I'd like to type in a MAC address (12 Charactors A-F 0-9) an have it display in the format 44-85-00-B2-5E-3B
    So I'd type in 448500B25E3B and the cell would display 44-85-00-B2-5E-3B
    How can this be done?

  10. A2 = x
    A3 = $
    B2=2
    B3=3
    can i custom format with formula like B2= 2x, B3=3$ B2 +B3 =5

  11. Hi,
    Is there a way to format numbers, so that the digit of the decimals appear with a smaller size or a different color than the integer part?
    Thanks a lot!

  12. How can I use format cell custom in Likert Scale? My interest is that I should key in a number but the cell should read the scale. for example, 1=Totally Disagree, 2=Disagree, 3=Undecided, 4= Agree, and 5= Totally agree. My interest is to key in a number e.g. 4 but the cell should read AGREE.

  13. Cool!! Nice explanation, thanks

  14. Suppose i have a text "Apples" in cell A and a negative number (1,234,567.55)in cell B
    Now iam clubbing these two cells using concatenate formula while converting the amount in to Millions.
    the result iam getting is Apples $ -1.23 M.
    =CONCATENATE(A1," ","$"," ",ROUND(B1/10^6,2)," ","M")= Apples $ -1.23 M
    My question is how can i get the negative number into a parenthesis like
    Apples ($ 1.23) M

    1. Hello Ram!
      It is impossible to make a custom number format in the form you want. Therefore, you need to use text functions.

      =IF(B1>0,CONCATENATE(A2," ","$"," ",ROUND(B1/10^6,2)," ","M"), CONCATENATE(A2," ","($"," ",ROUND(-B1/10^6,2)," ",") M"))

  15. Hi guys. I just wanted to say thank you for an incredibly informative page about excel formatting.
    I am stunned to see that despite the quality and breadth of formatting info provided, so many desperate people still need to send you exotic requests for formatting help, and they are genuinely stuck. I think there is a general issue in need of a better solution than what excel offers. Food for thought!

  16. Got a special need whereby i need to format data that contains numbers in a special format

    If the there are 3 numerical digits i need to display them as 3 digits
    If more the structure would be 000/0000

    I will be using them for formulas containing product codes

    How would i do them baring in mind I need the content to be exactly as formatted above

    1. Hello Ahmad!
      Your problem can be solved by converting the number to text.

      =IF(LEN(A3)>3,REPLACE(A3,3,1,MID(A3,3,1)&"/"),A3)

      I hope this will help

  17. Hi! I would like the fraction part of mixed fractions to be smaller font (ideally like the symbol). Example: 3¼ instead of 3 1/4. Is there any way to do this in Excel?

    1. Hello Naomi!
      In Excel, the number format can be set only for the entire cell, but not for its part. If the cell contains text, then you can select part of the text and set a special format for it.

  18. Hello !
    I wanted to convert "a row consisting of numbers ending with either Dr or Cr at end " to row consisting numbers with -ve wherever Dr and +ve wherever Cr occurs.
    ex row 23 Dr -23
    52 Dr to be coverted as -52
    72 Cr 72
    thanks

    1. Hello!
      I’m not sure I understood you correctly, because your abbreviations are not clear to everyone.
      The following formula should work for you

      =IFERROR(--SUBSTITUTE(A15,"Cr","",1),-SUBSTITUTE(A15,"Dr","",1))

      I hope this will help

  19. I need a number format to show numbers like following
    ABC/20/I/461
    ABC/20/P/5420
    ABC/20/K/489
    Now the First 7 characters are fixed i.e., ABC/20/ , next character is an alphabet then / and then a number is to be written.
    What should be the number format if we can avoid typing ABC/20/ portion and simply type like
    I461
    P5420
    K489
    and those are shown as
    ABC/20/I/461
    ABC/20/P/5420
    ABC/20/K/489

    1. Hello!
      It is impossible to separate the text from the number directly in the current cell and at the same time add additional text using the format. Use VBA. Using formulas, this can be done. But you will enter data in one cell, and you will receive the result in another cell.

  20. Hi Team,
    I'm currently working on converting different formate, but I didn't figure out, How do I convert 1467:1 into number formate.
    Thanks

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail.
      Please let me know in more detail what you were trying to find. Explain what you want to get - remove the colon, discard the characters after the colon, do the division, or something else?
      Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

  21. Sorry this is what i want to ask if string is 20SCSE171398 i want 101398 , first two digit and last 4 digit, how to remove in between 6 characters or digit

    1. Flash Fill could do the work without using any formula. I really dont know how to explain it right but you can search it for yourself if you are interested.

    2. Hello Ravi!
      To delete and replace several characters in the text, the REPLACE function is used. If I understand your task correctly, the following formula should work for you:

      =REPLACE(Q1,3,6,"")
      =REPLACE("20SCSE171398",3,6,"")

      Hope this is what you need.

    3. 201398 i want

  22. if string is 20SCSE1398 i want 101398 , first two digit and last 4 digit, how to remove in between 4 characters or digit

  23. I'm putting together a sheet with different number-formats and examples for myself.
    A little question: there's a format #.##0,00 and a format #.##0,00;-#.##0,00 (XL2010)
    What is the difference between those two? If a negative number is formatted with #.##0,00 it just gets a minus sign as expected. Why then would one need #.##0,00;-#.##0,00 ?
    Thanks in advance!

    1. Hello Marcel!
      Each number format can have up to four sections, separated with semi-colons. This structure can make custom number formats look overwhelmingly complex. To read a custom number format, learn to spot the semi-colons and mentally parse the code into these sections:
      1.Positive values
      2.Negative values
      3.Zero values
      4.Text values

  24. I use a format such as:
    [<1000000]0.0000," Kilo";[ 100milli
    0.0001 -> 100micro
    0.0000001 -> 100nano
    etc?

  25. If custom format consists of 4 sections of code separated by semicolons then how do I create a custom format with more than conditions 4 conditions as conditions are also separated by semicolons?

    1. Hello Abhishek!
      Custom Format indeed consists of 4 sections separated by a semicolon. Each section has its own assignment: 1 – format for positive numbers, 2 – format for negative numbers, 3 – format for zeros, 4 – format for text values. A user-defined number format can also contain up to two conditions which are entered in square brackets, e.g. [> 100] or [<= 100].
      When you use conditions in a user-defined number format, you redefine the first and the second sections.
      For example, Custom Format [Red] [<100] 0; [Blue] [> = 100] 0 means that values less than 100 will be displayed in red, and more than 100 – in blue. However, there can be only two of such conditions.
      To apply more than 2 conditions to the cell format, you may try to use Conditional Formatting which is well described in this blog post.

  26. Hey I added the following formula _(* #'##0_);_(* (#'##0);_(* "-"_);_(@_) but now it set's numbers like 333 like this '333 too. Can I somehow modify this so it doesn't include these?

    1. Hello Codru!
      As a digit grouping symbol, Excel uses the symbol that is specified in Control Panel -> Clock and region -> Region -> Formats -> Additional Settings -> Digit Grouping Symbol.
      Unfortunately, it is not possible to change that symbol using a custom number format. Please change it to ' via Control Panel.

  27. Hello,
    The excel formatting information that you provided on this site is amazing and so helpful.
    Is there a formula to make 4 display as 0004000
    Thank you very much.
    Sharon

    1. Hello Sharon!
      If you want to add three 0 to the left and to the right of number 4 and the number itself won't change, then you can try to use Custom Format.
      Please go to Format Cells, choose Number -> Custom Format and set the followig format:

      000###0"000"

      I hope my advice will help you solve your task.

  28. Hi, I have whole that I need to change to currency format.
    For example, 10816, I need to convert to 108.16.
    What Custom formula would I need to use to accomplish this?
    Thanks,
    AL

  29. I want 123A as 1/23A in excel custom format. Pl help

  30. Hi there, what's the formula if i want the 206,508 to be shown as 2.06508? thanks!

  31. Respected maim,
    I am trying customise cell value depending upon excel formula. But I couldn't. Can you help me. Just look below
    =IF(OR(ISBLANK(E11),ISBLANK(F11)),"NO","YES")
    NO AS RED COLOUR
    YES AS GREEN COLOUR

  32. Thanks a lot. How to navigate excel became clearer to me

  33. Thank you SO MUCH for this incredibly useful information!! It is so well presented and thorough!! I am glad that I have purchased your product and supported your efforts, keep up the great work!!

  34. Is there any way I could directly partially mask user input within the same input cell i?
    Meaning if user will to key in S12345678Z within A1 cell, it will auto mask to S********Z within A1 cell itself. Can Custom format in Excel be able to do so ?

    The only method I have successfuly done is 1 cell for User Input ( e.g A1) then masked result cell (E,g A2 using formula like =LEFT(A1,1)&"****"&RIGHT(A1,4)

  35. i have amount and i need format code for egyptian pound so i need cell with LE
    like 60LE

  36. i want to enter the employee number wherever in column a and it will show the name of the employee. how can i code that using macros vba?

  37. i want to add word after number...like (1 day) if i click 1 it outo be 1 day

  38. Hi,
    I am trying to copy tables with custom number formats from Excel to Powerpoint but when I do so, the ppt table will have what appears like doublespacing in each row. I have tried to format it in Powerpoint (cell margin, paragraph function, etc.) but it won't work unless I change the number format in excel. Only way i can copy-paste tables now is to embedd them or paste them as picture. I am using the newest Office 365, my colleague can do it with an older office version.
    Do you have any solution for this?

  39. Is it possible to display a fractional value in a different color than the integer preceding the fractional part?

    For example if I have the value in a cell of ... 42 15/16
    Is there a way to display the "42" in black and the 15/16 in blue?

  40. Hello. Here's a challenge:
    I'm looking for a solution to a problem that I have regarding regional settings.
    Is it possible to have my regional setting in place [.(period) as Thousands separator and ,(comma) as Decimal point] and have specific cells in my spreadsheet with: [.(period) as Decimal point and ,(comma) as Thousands separator]?
    I need to have charts and tables with two different settings (and language) in the same sheet...
    THANK YOU!!!!

    1. Ricardo Mateus, have you found a solution?

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

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

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

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

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

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

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

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

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

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

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