LinkBack URL
About LinkBacks
Is there any catch all custom number formatting that will give me the below results
3-> 3
3.5-> 3.5
3.33333333-> 3.33
I messed around with various combinations of 0, # and ? and couldn't get it
Is there any catch all custom number formatting that will give me the below results
3-> 3
3.5-> 3.5
3.33333333-> 3.33
I messed around with various combinations of 0, # and ? and couldn't get it
Last edited by lordfa9; 02-02-2019 at 01:34 AM.
conditional number format
1. =MOD(A1,1)=0
2. =AND(LEN(MOD(A1,1)*10)=1,MOD(A1,1)>0)
3. =LEN(MOD(A1,1)*10)>1
Last edited by tim201110; 02-02-2019 at 07:44 AM.
Sorry for off-topic interjection:
Tim - may I remind you that although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Why use 3 conditional formatting rules, Tim? You only need 1.
Formatting cells as general, then applying CF rule
=MOD(A1,1)
With a custom format of 0.##
Where A1 is replaced with the reference cell of the 'Applies to' range, will do as the OP has asked.
deleted by tim
Last edited by tim201110; 02-02-2019 at 09:33 AM.
I tried 0.## it doesnt work for me because it makes 3 appear as 3.Originally Posted by jason.b75
UPDATE: googled around and apparently that is a common problem. Hope I don't have to use TRUNC
Last edited by lordfa9; 02-02-2019 at 09:35 AM.
Not if you do it the way I suggested.
Format the cells as 'General'
Format the decimals as 0.## by using conditional formatting with the rule =MOD(A1,1) with A1 adjusted to match your data range.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks