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

  1. Hey, first let me thank you for all these excellent ideas and suggestions!

    and I apologize if this has already been asked...

    I'd like to customize a text field using the double quotation marks (" ") in the format, which is easy enough. (I'm using the format "Invoice #: "@ )
    (I'll use Invoice #: P240200109 as an example)
    Using that format, the field appears as it should.
    My next step is to copy that field and paste it into another database.
    However, I'd also prefer to copy the field *without* copying the "Invoice #: " part, (i.e., only copying the 'P240200109' portion)
    I'm unable to find a way to do this, other than editing the required cell, and copying the data through the cell/formula bar.

    I hope what I'm asking above makes sense.
    Any suggestions?
    Thank you!
    Michael

    1. Hi! If you are using the custom cell format "Invoice #:"@ , the value P240200109 is written in the cell. If you copy this value using a cell reference (e.g. =A2), change the format to Text in the cell with the formula. Or change the format using the TEXT function:

      =TEXT(A2,"@")

      I hope that the advice I have given you will help you solve your problem.

  2. Is it possible using Format Cell to only show 4 digits on the right?
    What I have now is: Y1/1234 and I need only the "1234".
    I know it would be easier to create a new column with formula but I can't have more columns on my file.

    1. Hi! Unfortunately, the Excel cell format does not allow you to display only part of the text string in a cell.

  3. Hopefully I did not miss a reply that answers my question as I read through this thread. If I did, I do apologize.

    I make reports in one currency and also must provide them in a second currency. I am hoping I can find a way to do this simply thru formatting without having to create separate cells in which to display the results as I currently do. So the desire is to display a number in a given cell after dividing by a second fixed number (ideally pulled from a named cell "Exchange rate").

    It seems like it should be doable, but I have been unsuccessful so far.

    Thank you and Ablebits for all you do to help us for whom Excel is a necessary tool!

      1. Thank you for dashing my hopes! :) I will waste not one more brain cell on this and will continue on as I currently do. :)

  4. Great post!
    Is there a custom formatting way to present only the first 4 characters of the text value of a cell using @ with some syntax?
    WBDK Worlds Best Desktop Keyboard -> WBDK

  5. Hi, I´ve trying to format the below but i can´t seem to do it. Could you help me, please?

    I want to show:

    590000 as 0'59 or 1375000 as 1'38

    with the "upper" comma and 2 decimals after it (rounded)

    thanks a lot-

    1. Hi! To show a number in millions, you can use this custom format
      #,##0.00,, or #,##0.00,, "M"
      However, you cannot replace the decimal separator in a number (.), which is set in the system settings of your computer, with a different one (').

  6. I want to convert decimals in to crores

    1. [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0

      1. One "Crore" is written as 1,00,00,000; it is equal to 10 million.

        1. Hi! With Excel's numeric format, you can show a number in thousands or millions. For example: #,##0,," M" - in millions. You cannot show a number in tens, hundreds, tens of thousands, or tens of millions.

  7. I want to display only after decimal values only. Is it possible in excel custom formating

    1. Wow, what a fantastically-written piece!

      I landed on this page as a result of trying to intuit an answer to this:

      I’ve been handed a spreadsheet that’s utilizing the $#.00,, \M format. I’m dealing with hundreds of millions of dollars, so this is fine.

      [I’ll add here that the formula is being used for a bar chart axis.]

      My problem is that my data range has crept from the hundreds of millions into the billions, and I don’t like my axis max (based on my data set) displaying “$1200 M” instead of “$1.2 B.” (Or $1000 M instead of $1 B, either.)

      Can I customize the canned format by slipping in somewhere a qualifier such as [>1000000000]… or [>1.0E9]…., such that my axis can ascend, “$100 M, $200 M,…$900 M, $1 B [I would like $1.0 B, actually], $1.1 B, etc.?

      Thank you for any help you or anyone can provide.

      Now it’s on to your Conditional Formatting page! (This is great stuff here, Svetlana!)

      Thank you!

  8. I want to insert +1 in a cell. While using add formula/ multiply formula , only number has to be taken but not symbol. Please furnish a solution

  9. ##0% _P;(0%)_P;

    #,##0_P;(#,##0)_P;;

    I prepare Management Accounts, and have just looked behind the formatting of Variance values in a table, and was surprised that I had used a letter P in the formatting of my numbers.
    I typically use _) so that my numbers don't abut the right hand column edge but have no idea why sometimes I have deliberately used a P.

    Do you have any idea what the purpose of the P is for please ? I must have seen it in a You-Tube video about three years ago !

    Thank you !

    1. Hi! The "_" character adds a space after the number. The letter "P" or any other letter in your custom format does not change anything, as it is replaced by a space.

  10. Hi
    I would like to insert an "Up" or "Down" arrow in the same column where I get stock market data, data changes every minute.
    The number of rows can go upto 300 by the close of market
    Thanks

  11. Hi;
    How can i set a cell to accept just 10 digit number?
    )For example, if I enter a 6- or 14-digit number, I get an error that the number of digits entered is wrong!(

  12. Hi there,

    How do i format a cell to display PRE122, CP8, PP11 as PRE122, CP008, PP011?

    Thanks!

  13. Hi Alex, Thanks for this information. Very helpful.
    I was wondering if you knew any way to display part of a cell via the number format (I can do it via formulas and a helper column, but would rather not). So I have a series of numbers formatted like so:
    017.2022.00000096.001
    017.2022.00000097.001
    017.2022.00000098.001
    017.2022.00000099.001
    017.2022.00000100.001
    017.2022.00000101.001
    017.2022.00000102.001
    017.2022.00000103.001
    017.2022.00000104.001 etc.
    Is there any way to have the cell just display the main number? So in the above data, the cell would display:
    96.001
    97.001
    98.001
    99.001
    100.001
    101.001
    102.001
    103.001
    104.001 etc.
    Thank you

  14. Hi!! In excel, I want to place the axes of the graphs with exponentials, such as: 10^3. But I also want the number 3 to appear as an exponential and for the symbol "^" not to appear. Is this possible?

  15. This is the number 2854-951-2023 & 2854-1231-2023
    How may i make 2854/000951/2023 & 2854/001231/2023

  16. Hi! I have a question regarding using dashes for zeroes. Is it possible to format these dashes? I like using them but they seem a little short. I'd like to make a longer dash or at the very least bold it. Is this possible to do in the number format code?

    1. Hi! Specify the character you want in section 3 format code. See the instructions in the article above. Here is an example:

      #,##0;-#,##0;"–"

  17. Hiya, I have a format I need help with, I have cells that I need to enter a 4 digit number and a letter either at the start or end i.e. T4015 or 4015T.

    I need a custom format that will allow the cell to display the number but hide the letter. I need the letter in the cell but don't want it to be seen.

    Is this possible?

  18. I am using #.##,, "M" to represent numbers in millions but I don't want the decimal point shown on exact millions. For example, 3,000,000 shows up as 3. M instead of just 3 M. Everything else behaves as I want. Is there any way to do this?

    1. Hi! If you want the decimal point not to be displayed for exact millions and displayed for other numbers, use conditional formatting. Use a separate number format for exact millions.
      The conditional formatting formula might look like this:

      =MOD(A1,1000000)=0

  19. Hello: I would like to apply a format to a cell in which when entering a value it adds the mm suffix and express it in inches, example: 63.5 mm (2.5 in). Is this possible?

    1. Hi! You can use the format to add symbols to a number (e.g., "mm" or "in"). But you cannot use the format to do math operations on numbers.

  20. Hi,

    I'm having an issue and I hope you can help me. I'm writing a paper and the reviewers asked me to write the numbers in the charts as "1000" and "10,000".
    This means a comma-separated value for a 5-digit number or more and none for less.

    Do you have any suggestions?

    Thank you

  21. what is the format crore value with 2 decimal

    input
    57761870

    Output required
    5.78 CR

    1. Hi! You can use number formatting to show a number in thousands or millions.
      For example,

      #,##0.00,," M"

      But you cannot show the number in tens of millions as you want.

  22. Hi, Alex! Awesome article, awesome knowledge of Excel. Thank you for posting and answering questions! Kurt

  23. Hi, I am using Excel for mac. I am trying to custom format a list of fractions, such as:
    1 1/4
    2 3/8
    11/16
    3 5/8

    etc.

    When I apply the custom code of # #/# it is not working, the info still comes in as either dates or decimals.

    How can I force excel to treat all of these the same and have the same formatting?

    Thanks-

  24. How can I see the following format DVO-000. For example it should show as DVO-001 or DVO-010 or DVO-100.

      1. Cell value is DVO123
        I want to custom format to DVO-123.
        It seemed custom format is not working text string followed by a number

  25. Hi. How about showing letter and number characters?

  26. How to change the number format from Dr and Cr to + and -

  27. General number is 15
    How I found 45 by custom format cell ???

  28. Mohon bantuannya saya ingin membuat format angka
    1-2
    1-22
    Saya mencoba #-##
    Tapi hasilnya
    -12
    1-22

  29. In Excel I have a formula that equals ($3,55,12,059). I want to just show ($35,512,059 )

  30. Excellent tutorial. I have a question on the example of text added to custom format. On the example Apples 1 day Shipped in 1 day

    I would like to know if it is possible a custom format that refer to the cell location (H2) where is "Apples" such as "Apples are shipped in 1 day" .
    Thank you for your help,
    Roberto

  31. In Excel I have a formula that equals ($35,512,059). I want to just show ($35512) so I changed the number formatting to custom $#,##0,_);($#,);--_) now I just want to add a comma so it shows ($35,512)

    What's the number formatting that I should use?

  32. I want to center aligned when cell value is 0 (zero), else right aligned. How can it be done?

  33. How to format a number with specific colored text (like red) e.g 2.695 format to 2.7a - the letter a is specific and color red. Thanks

  34. Hello I am trying to change the format of a date on a csv file.
    the format I want is yyyy-mm-dd hh:mm:ss.000
    I change it through custom type and save csv again, but when I reopen the csv the format of the date is the previous one.
    How can I permanently change the type of date to this format "yyyy-mm-dd hh:mm:ss.000" ?

    1. Save the file as an Excel file, do the formatting, then resave as a csv.

  35. Is it possible to create number formats in a start up file and then use them in any other excel file that i open?

    1. Hi!
      Simple solution: just copy your format with the Format by Pattern tool (brush) from book to book.
      Difficult solution: create a new book, add your format to it, save the book as a template (xltx extension) to the Custom Office Templates folder. When you create a new file, you will see this template in the Personal section. Create a new file from this template.

  36. how can i set a costum format to have a number like this IR-06-2541-2451-6582-4895-5842-01??

    1. Hi! What you wrote is text, as there are 2 letters. There is no possibility of separating each 4 digits with the number format in Excel. Use the REPLACE function to insert "-" at the exact position in the text string.

      1. How about this:
        "IR-"00-0000-0000-0000-0000-0000-00"??"

  37. Great Explanation of custom number formats. However, there is something I am confused by that may have been overlooked in this explanation. Does Custom conditions make negative formatting irrelevant? For example:

    [=1]?" mile";# ?/?" miles"

    My mind is still stuck on the fact that anything that comes after the first semi colon is reserved for negative numbers. Is there a hierarchy thing that cancels that rule out.

    Also, If you set a conditional format for a positive number does it automatically apply to negative numbers by treating the value as an absolute?
    Example: [>=1000000]_($* #,,"M";_($* #,,"M"

    This seems to apply to both positive 1 million as well as negative 1 million

    1. Hi! The first and second sections of the default formatting pattern are used to format positive and negative numbers. You can use other conditions in these sections. If you specify one condition, the second section is used for all other values. If you specify two conditions, the third section is used for all other values. For example,

      [=1]?" mile"; [<0]-# ?/?" mil";# ?/?" miles"

      1. Thank you so much for your explanation!

  38. Hi
    I want to display the letter "k" as the unit of kilograms, and its done with below format without backslash
    format" 0 K"
    It seems that the use of backslash is not mandatory isn't it?

    1. Hi! Backslash must be placed before characters that are used in the number format pattern. In other cases, you may not use it.

  39. how to format this 117610600000 its 117 billion to millions numbers only? Anyone can help and will appreciate it much. Thanks.

  40. I was wondering how to create a column where text is right aligned with positive and negative numbers. I have used this custom format to align the numbers, _*#,##0_);_(* (#,##0);_(* " "-"??_);_(@_) but the text will not align and if I use the indent feature for the text, it does not align properly.

  41. I could not found this in all the comments but maybe I messed some.

    I need do format from a CONCATENATE function with embedded line breaks, 2 numbers (or one of them if the other doesn't exists)

    The only part I'm having trouble is the TEXT function format part within IF clauses, as the numbers vary in size.

    the numbers should have the following formats for each variable number
    9
    99
    9 99
    99 99
    9 99 99
    99 99 99
    9 99 99 99

    How can I add multiple conditions to format these #\ ## or ##\ ## or #\ ##\ ##, etc.?

    I can set a condition to divide it in two, but I can't divide it into multiple conditions of the same nature [99] [<=999] #\ ##; etc.

    Is it possible? Or a work around?

    Thanks in advance for any help. I haven't found it any reference or tutorials yet.

    1. Hi!
      If I understand your task correctly, to split a number into hundreds (2 digits each) try this custom number format

      ## ## ## ## ## ##

      1. Thanks for your input. That format provides this (after not the desired format):
        9
        99
        9 99 not 99 9
        99 99
        9 99 99 not 99 99 9
        99 99 99
        9 99 99 99 not 99 99 99 9
        99 99 99 99
        9 99 99 99 99 not 99 99 99 99 9

        Is there a workaround this? I tried to split intervals with conditions to aggregate intervals, but It appears one cannot conjugate in the same format filter more than 1 condition of the same type [>x] with [<=xx] etc. tried the & operator also.

        1. found out a workaround (conceptually, but one could translate it to excel formulas quickly):

          evaluate char number of text string, IF uneven format text string "##\ ##\ ##\ ##\ #" ELSE [even] format text string "##\ ##\ ##\ ##\ ##"

          Sorry fort not posting the formulas but I don't have to time to search for the english functions names of those I don't know from memory (I'm using PT-PT version).

          1. It followed from your questions that you want to change the custom format of the cell in which the number is written. If you were to say that the number needs to be shown in another cell, this completely obvious solution would be offered right away.

    2. Sorry for the typos *missed, etc.

      1. gosh... [>99] [<=999] #\ ##, etc...

        1. Wrong ID of format numbers, sorry

          The one I wanti is this one:

          9
          99
          99 9
          99 99
          99 99 9
          99 99 99
          99 99 99 9
          99 99 99 99
          99 99 99 99 9

          The other version is easy to format in TEXT function with "#\ ##\ ##\ ##\ ##\ #0" Just have to be care to let the single digit option #\ and enough repeats of ##\ for the highest length number...

  42. Hi,
    I'd format a single excel file (not all mine ) a different international format (decimal separator). I do not want change operating system seetings nor excel settings in the advanced menu . thanks

  43. Hello,

    I have a large digit of numbers that always starts with 55, and I need help creating a Format Cell that would color in blue (or bold) any numbers after 55 and before the last 7 digits (the numbers in the seven digits sometimes remain the same). Here is my example 5512345673806327, and the numbers to color in blue are 1234567. Thank you!!

  44. I want to add a minus sign to the beginning of a number and have one decimal place. If I enter 12, I'd like it to show as -12.0. All I've been able to accomplish is:
    "-"@
    It gives me the minus sign, but turns the number into text and I haven't been able to figure out how to add the decimal point.

      1. Awesome thank you!! How was I supposed to infer this?

  45. Hi,
    I want to know how can I use auto numbering in my packing list. Such as my packing list should show.

    Serial No of Carton
    1-2 1
    3-4 1
    5-7 3

    Can I automate the process? it took me while to type the data into the 'serial number' column.

    Please help

  46. I want my table to out put the standard error with +/- the error, is this possible?

  47. Hi Alex,

    This article gives me new insights to do custom format in excel that l think impossible before. Thanks a lot for that.
    I am trying to make this format showed in the cells

    3 digits number, forward slash, fixed character lNV, forward slash, fixed character DJE, forward slash, character, forward slash, 2 digits number
    Example: 123/lNV/DJE/VII/23

    Can l write them like these Alex:

    ###/INV/DJE/General/##

  48. Is there a way for showing the data as 2^#?
    I want to show 1024 as 2^10 and 1 as 2^0.

    1024,00000000000 = 2^10
    512,00000000000 = 2^9
    256,00000000000 = 2^8
    128,00000000000 = 2^7
    64,00000000000 = 2^6
    32,00000000000 = 2^5
    16,00000000000 = 2^4
    8,00000000000 = 2^3
    4,00000000000 = 2^2
    2,00000000000 = 2^1
    1,00000000000 = 2^0
    0,50000000000 = 2^-1
    0,25000000000 = 2^-2
    0,12500000000 = 2^-3
    0,06250000000 = 2^-4
    0,03125000000 = 2^-5
    0,01562500000 = 2^-6
    0,00781250000 = 2^-7
    0,00390625000 = 2^-8
    0,00195312500 = 2^-9
    0,00097656250 = 2^-10

  49. Is there a way to key in, say, 327 and have it automatically convert to 0.327 without using formulas or conditional formatting? I am manually keying many numbers and don't want to have to type the decimal. Nor do I want to manage any other columns.

  50. Hi,
    I am looking for an opportunity to generate an AWB stock.
    An AWB number consists of 3 numbers, a minus, and 8 numbers. a total of 12 characters
    The first 3 numbers and minus (-) are always the same.
    The next 7 mummers count up as usual
    The last number counts up from 0 to 6, and starts at 0 again

    111-12345670
    111-12345681
    111-12345692
    111-12345703
    111-12345714
    111-12345725
    111-12345736
    111-12345740
    111-12345751
    111-12345762
    111-12345773

    1. Hi!
      If I understand your task correctly, try the following formula:

      ="111-"&SEQUENCE(100,1,1234567,1)&TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,7))

      1. Thanks for your help, this works good

        1. Hi,
          I notes something wrong in the formula (maybe I was not clear in my fist question)
          This do not always start with a 0 in the end.
          It can be like all these 3 example

          111-12345670 111-23456781 111-3456792
          111-12345681 111-23456792 111-3456793
          111-12345692 111-23456803 111-3456804
          111-12345703 111-23456814 111-3456815
          111-12345714 111-23456825 111-3456826
          111-12345725 111-23456836 111-3456830

            1. Im sorry. I have one more question
              How do I do it if the numbers start with 0?
              like:
              111-00005252
              111-00005263
              111-00005274
              111-00005285
              111-00005296
              111-00005300

              If I use the formel will it show:
              111-5252
              111-5263
              111-5274
              111-5285
              111-5296
              111-5300

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