Determing Len of Numbers with Whole Numbers involved

[Guest]

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

 

[Niek Otten]

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


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

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

 

[Guest]

If your value is in A1 use
=LEN(TEXT(A1,"$#,##0.00"))
rather than
=LEN(A1)

This will give the length "as formatted"

 

[Guest]

In a similar vein, you could use just

=LEN(DOLLAR(A1))