Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
I'm using the & function to draw several cells into one to produce a product
description. One cell has user input format of 0.00. If the user inputs a value without decimals, ie 4.00, I want the final result to be 4 in the product description. If the user inputs say 3.15 then the final result in the product descripton should be 3.15. How do I check a cell to see if there are numbers to the right of the decimal. Is there a simpler way than say using Integer, subtract from original, compare to zero and then format with if statement??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
Once you use & to concatenate numbers with text, you will get the value which
was entered in the cell and not as the cell is displayed... So in this particular case you don't have to anything special to get what you want... You can search for decimal point and the subtract that from LEN of the value to get number of decimals. Of course you have to take care of cases when there are no decimals. "gtb" wrote: I'm using the & function to draw several cells into one to produce a product description. One cell has user input format of 0.00. If the user inputs a value without decimals, ie 4.00, I want the final result to be 4 in the product description. If the user inputs say 3.15 then the final result in the product descripton should be 3.15. How do I check a cell to see if there are numbers to the right of the decimal. Is there a simpler way than say using Integer, subtract from original, compare to zero and then format with if statement??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number formatting
Hi,
You already have an answer that with concatenation this isn't necessary, but I will answer your question anyway =MOD(A1,1) This returns the decimal portion of a number or =MOD(A1,1)0 This return true if there are any non zero numbers to the right of the decimal point. =LEN(ROUND(MOD(A1,1),14))-2 This returns the number of characters to the right of the decimal point as long as that is less than 15 digits. If these help, please click the Yes button. Cheers, Shane Devenshire "gtb" wrote: I'm using the & function to draw several cells into one to produce a product description. One cell has user input format of 0.00. If the user inputs a value without decimals, ie 4.00, I want the final result to be 4 in the product description. If the user inputs say 3.15 then the final result in the product descripton should be 3.15. How do I check a cell to see if there are numbers to the right of the decimal. Is there a simpler way than say using Integer, subtract from original, compare to zero and then format with if statement??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number Formatting | Excel Discussion (Misc queries) | |||
formatting cell number based on previous cell number | Excel Discussion (Misc queries) | |||
Number formatting | Excel Discussion (Misc queries) | |||
Number Formatting | Excel Worksheet Functions | |||
Number formatting | Excel Discussion (Misc queries) |