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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. How to display 0.99 as 0 and not 1
    Or 1.8897656788 as 1 and not 2
    I need the value do i cant use int or trunc or round function. Its only a matter of how to display

  32. how can I show in a cell if I type 1 it shows me 1 no, if I type grater than 1 it should show me 2 nos

  33. Hi,
    I use IF Function in the cell so that it will show me "yes" or "no"
    The thing is that I want if the formula show "yes" I want it in a red color... been trying to format the cells but nothing work... could you tell me what should i do? many thanks

    1. Sorry, no need to answer... I already got the solution, changing the if formula into a code of 1,0 then formatting the cell to translating the code into a text and coloring according to my condition

  34. Hi,

    Looking to create a format to change a 12 number field into something like this
    123456789012
    to
    ABC1234_123_12345

    Cant seem to find if and how I can achieve this.

    Thanks in advance
    Stephen

  35. Hi Svetlana,
    I use Excel to keep track of job numbers for my work. I am using Office 365. Some of these job numbers begin with a 0 and are 14 digits long, but not all begin with 0. I need the 0 in there and have been doing fine with using 0#############, however, when I create a new worksheet, the custom format is no longer in my excel. Is there something I need to do in order to save it permanently so I don't have to keep creating the format each time I create a spreadsheet? I'm not sure if this information helps but the worksheets I create are exported from a database online so it gets saved in HTML format in my downloads folder first then I resave it in documents folder as an xls.

  36. Simply brilliant. I wan completely lost in the excel 'wildcards' and your explanations and examples saved me.
    BIG thank you

  37. Hey,
    I'm working on a currency (euro) formula, but it's for dutch customers. This meaning that instead of it being written like 1,234.56, we use a period as the thousands separator, and a comma for the decimals. What formula could I use for this? I tried >> "€"\ #.##0,00;[Red]"€"\ \-#.##0,00 << but it keeps jumping back to having the comma as a thousands separator, and a period for the decimals.
    Thanks!

  38. Hi,
    How can i use custom number formatting to display 12,34,45,678.00 to 12.34 in cell without dividing the value.
    Please help
    Thanks
    Sharan

  39. Hi,
    How can i use customer number formatting to display 12,34,45,456.00 to 12.34 in cell with dividing the value.
    Please help
    Thanks
    Sharan

  40. Hi,
    0,, "Mbps" will display 10000000 as: 10Gbps
    How do i display:
    0.000001 as 1 usec
    ?
    Thanks

    1. Of course i meant:
      0,, "Mbps" will display 10000000 as: 10Mbps

  41. Sir
    Thanks in advance.
    We need a single space between word in HDFC0000440 like H D F C 0 0 0 0 4 4 0 through cell formatting only. Please adviese.

    Ram Chander

  42. I have a spreadsheet of part numbers that needs separating into blocks. 0123456789 becomes 0 123 456 789. This works for most of the spreadsheet, but a few numbers refuse to convert. I am using the CUSTOM feature like this ==> 0 000 000 000. Some, but not all of the failed results have a letter in them. i.e. 0 123 A56 789. Any thoughts?

  43. I want to use fraction in excel with value 1750/28000 and display as 1750/28000, as number type. Best regards Janko

  44. Hi,
    I need your helps to convert 100 to 10^2.
    Thanks

  45. Hi is there anyway to put a Line break into custom number formats. I want to do this so I can display the Date and time on my graph. What I current get is:

    00:00 Mon 10 Jul 2019

    What I want to display:
    00:00
    Mon 10 Jul 2019

  46. I want to display the tenths place if it is *NOT* zero and I want to *NOT* display the decimal point if the tenths place is zero. How can I do that?

    For example, 20.23 should be displayed as "20.3" but 20.03 should be displayed as "20" without the quotes.

    In other words, I only want the decimal point if and only if a non-zero digit is in the tenths placeholder.

    Any help is appreciated, Michael Carney

  47. How can i format part of text in a single cell that contains a formula result.
    eg. a cell contains formula result like "Iam 25" i want to format 25 as bold

  48. Hello there,
    I am trying to define the following range from Zero to one:
    - Negative numbers become automatically 0.01
    - Zero becomes automatically 0.01
    - Positive numbers >1 become 1
    Could you please give me some hints on how to define the custom format?
    Thanks a lot!

  49. Hi,
    How can i change number format in this way?
    For Eg: Take a let us take a number 5000000. Currently in excel its showing like 5,000,000. I want the "commas" in this way.. like 50,00,000.

  50. Hi,
    I have a few questions maybe you could help;
    1. I prepared a table in excel like a client data form and in the name and surname columns I want the proper case (Joe not JOE). I used Proper formula and data validation but it doesnt work.
    2. How do I avoid duplicate entries automatically? ( I put a full name column that concatenate name and surname data).I want it to check and stop "John Smith" double entries for instance. How is it done with data validation custom formula?
    3.Is there a template way for e-mail entries as per data validation like ________@_________.com/ru/en etc?
    4.I am also having trouble with dependent data validation entries. Suppose there are two types of buildings and first one has 5 floors while the second has 6. I put data validation list for the blocks but for the next column I want it to choose 5 floors for Block A and the 6 floors for Block B but the dependent list isnt working.Please kindly advise.
    5. I used row formula for autonumbering for client id # and I use vlookup for Client name and it is working but if the line is blank it gets the row number for client Id. I mean like 20090001 and 20090003 instead of 20090002. I want it to continue with consecutive numbering. How is it done?
    I kindly ask for your advice.
    Thank you & Best regards

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