Determing Len of Numbers with Whole Numbers involved

[BruceG]

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

"BruceG" <Bru...@discussions.microsoft.com> wrote in message news:C1466283-F006-4B90...@microsoft.com...

[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

--

Dave Peterson

[Gary''s Student]

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

[daddylonglegs]

In a similar vein, you could use just

=LEN(DOLLAR(A1))