[RESOLVED] Format cell

[billhuard]

Hi,
I'm trying to format cell to number format with no decimal.

The .numberformat "0" works for values that are already set as number, but the values set as text(the cell is text and value a number) do not convert to number with no decimal.

thanks

[salvelinus]

If the cell is text so is the value, even if the text is numeric. You can't apply numeric formats to text in the way you're trying to do. You'll have to either convert the cell to text or apply a text method to the text.

[billhuard]

That's what I believed.

So here is how I did to get text to become number.

Take the cell and multiply by 1.
This way, all text cells become number cells and then I NumberFormat it to "0"

[salvelinus]

Great. There's a Trunc (for Truncate) function you might want to check out as well.
From Excel 2K help:

Truncates a number to an integer by removing the fractional part of the number.

Also, at least for number cells, formatting doesn't change the actual value, but will round the value.
For example, enter 2.5 in a cell. Format the cell for zero decimal places. The cell now shows 3 because it rounded up. But 2.5 is still the actual value in Excel, which you can see in the formula bar. If you multiply the cell by 2, you'll get 5, not 6.
Trunc(2.5) will give you 2, but will show 2.00 if the cell format is 2 decimal places.