+ Reply to Thread
Results 1 to 5 of 5

Special custom number format

  1. #1
    Registered User
    Join Date
    02-18-2022
    Location
    Bulgaria
    MS-Off Ver
    MSO 2019 PRO PLUS
    Posts
    4

    Question Special custom number format

    Hello guys,

    generally, I know how the custom number format works.
    Now, I intended to show whole numbers without point and zeros, and the rest of the numbers as they are.
    Here an example:

    7.00 -> 7
    7.50 -> 7.50
    8.00 -> 8
    10.00 -> 10
    10.50 -> 10.50
    11.00 -> 11
    11.20 -> 11.20

    I couldn't find a combination to achieve it.
    Do you think it could be made with custom number formatting?

    Thanks in advance!

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,445

    Re: Special custom number format

    Hi,

    You could use conditional formatting...two rules....

    =MOD(A1,1)>0 set number format to "0.00"

    =MOD(A1,1)=0 set number format to "0"
    Last edited by sweep; 02-15-2023 at 10:17 AM.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,104

    Re: Special custom number format

    How about using CF with two rules
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and format the cell to show "0" & then
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and format "0.00"

  4. #4
    Registered User
    Join Date
    02-18-2022
    Location
    Bulgaria
    MS-Off Ver
    MSO 2019 PRO PLUS
    Posts
    4

    Re: Special custom number format

    CF did the trick, thank you very much !

    Actually, one CF rule was enough =MOD(A1,1)=0 as the dropped numbers out of the rule should remain general formatted.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,104

    Re: Special custom number format

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to change Pivot Chart axis number format to a custom date format
    By paulma1960 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-11-2023, 03:16 PM
  2. Format Cells -> Custom Number with Custom Color
    By Cardan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2021, 08:19 AM
  3. Replies: 5
    Last Post: 01-25-2020, 05:18 PM
  4. Need formula or code to convert number(s) from custom format to number format
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-12-2015, 07:44 PM
  5. Excel custom format changes to special
    By elevisse in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-08-2012, 02:49 PM
  6. special number format
    By sp1974 in forum Excel General
    Replies: 2
    Last Post: 03-03-2010, 03:59 PM
  7. how do I add phone number format as a permanent custom format?
    By frustratedagain in forum Excel General
    Replies: 3
    Last Post: 02-03-2006, 11:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1