Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determing Len of Numbers with Whole Numbers involved
I am at a loss on this one -
I am working on file that has a column of numbers in it. I need to make this data fit in a fixed length field which is not a problem but what I have is the LEN function returning less then the 2 decimal points when it is a whole number even though the format is 2 decinmal points Is there a way to tell me when a number is a whole number so I can use an IF stmt to add ".00" to the formula I am working on. Example - $143.29 7 $144.63 7 $69.00 3 Issue I want it to be 6 or tell me that it is a whole number $0.00 2 Issue I want it to be 5 or tell me that it is a whole number $69.63 6 $68.51 6 Thanks in advance for any help. Bruce |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determing Len of Numbers with Whole Numbers involved
Hi Bruce,
I don't understand why you get 7 as the length for $142.29, unless it is text instead of a number. Anyway, try =LEN(TEXT(A1,"$0.00")) -- Kind regards, Niek Otten Microsoft MVP - Excel "BruceG" wrote in message ... |I am at a loss on this one - | | I am working on file that has a column of numbers in it. I need to make this | data fit in a fixed length field which is not a problem but what I have is | the LEN function returning less then the 2 decimal points when it is a whole | number even though the format is 2 decinmal points | | Is there a way to tell me when a number is a whole number so I can use an IF | stmt to add ".00" to the formula I am working on. | | Example - | | $143.29 7 | $144.63 7 | $69.00 3 Issue I want it to be 6 or tell me that it is a whole number | $0.00 2 Issue I want it to be 5 or tell me that it is a whole number | $69.63 6 | $68.51 6 | | Thanks in advance for any help. | | Bruce | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determing Len of Numbers with Whole Numbers involved
In VBA?
with worksheets("sheet999") if int(.range("a1").value) = .range("a1").value then 'whole number else not a whole number end if end with You may just want to format the value the way you want: with worksheets("sheet999") msgbox format(.range("a1").value, "000000.00") end with BruceG wrote: I am at a loss on this one - I am working on file that has a column of numbers in it. I need to make this data fit in a fixed length field which is not a problem but what I have is the LEN function returning less then the 2 decimal points when it is a whole number even though the format is 2 decinmal points Is there a way to tell me when a number is a whole number so I can use an IF stmt to add ".00" to the formula I am working on. Example - $143.29 7 $144.63 7 $69.00 3 Issue I want it to be 6 or tell me that it is a whole number $0.00 2 Issue I want it to be 5 or tell me that it is a whole number $69.63 6 $68.51 6 Thanks in advance for any help. Bruce -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determing Len of Numbers with Whole Numbers involved
If your value is in A1 use
=LEN(TEXT(A1,"$#,##0.00")) rather than =LEN(A1) This will give the length "as formatted" -- Gary's Student "BruceG" wrote: I am at a loss on this one - I am working on file that has a column of numbers in it. I need to make this data fit in a fixed length field which is not a problem but what I have is the LEN function returning less then the 2 decimal points when it is a whole number even though the format is 2 decinmal points Is there a way to tell me when a number is a whole number so I can use an IF stmt to add ".00" to the formula I am working on. Example - $143.29 7 $144.63 7 $69.00 3 Issue I want it to be 6 or tell me that it is a whole number $0.00 2 Issue I want it to be 5 or tell me that it is a whole number $69.63 6 $68.51 6 Thanks in advance for any help. Bruce |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determing Len of Numbers with Whole Numbers involved
In a similar vein, you could use just
=LEN(DOLLAR(A1)) "Gary''s Student" wrote: If your value is in A1 use =LEN(TEXT(A1,"$#,##0.00")) rather than =LEN(A1) This will give the length "as formatted" -- Gary's Student "BruceG" wrote: I am at a loss on this one - I am working on file that has a column of numbers in it. I need to make this data fit in a fixed length field which is not a problem but what I have is the LEN function returning less then the 2 decimal points when it is a whole number even though the format is 2 decinmal points Is there a way to tell me when a number is a whole number so I can use an IF stmt to add ".00" to the formula I am working on. Example - $143.29 7 $144.63 7 $69.00 3 Issue I want it to be 6 or tell me that it is a whole number $0.00 2 Issue I want it to be 5 or tell me that it is a whole number $69.63 6 $68.51 6 Thanks in advance for any help. Bruce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Worksheet Functions | |||
How to generate sets of random numbers without having duplicates | Excel Worksheet Functions | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) |