TEXT Function and decimal places

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BoRed79 - 02 Jul 2007 16:54 GMT

Hi.

I am using the text function, with the following formula:

=TEXT(Data!A10,"##.#%") - where Data! refers to the sheet that the data is on

This is working fine.

However, some of the for some of the cells that I will be using this on,
there is a zero at the end of my value e.g. 8.0%.

When this formula returns the result it returns it as 8.%

Is there a way to ensure that it brings back the zero at the end as well, as
I will be using the data in a report (and the excel document and word
document will autoupdate via a macro).

Thanks.

Reply to this Message

Rick Rothstein (MVP - VB) - 02 Jul 2007 16:59 GMT

> I am using the text function, with the following formula:
>
[quoted text clipped - 9 lines]
>
> Is there a way to ensure that it brings back the zero at the end as well,

The # sign puts a number or, if no number, nothing in its position; a 0
(zero) put a number or, if no number, a zero in its position. So, use
this...

=TEXT(Data!A10, "0.0%")

instead (notice there is only one 0 in front of the decimal point... larger
numbers will fill in automatically. The same would have been true for your
original formula (assuming it is what you would have wanted... only one #
sign in front of the decimal point was necessary).

Rick

Reply to this Message

Peo Sjoblom - 02 Jul 2007 17:00 GMT

Is there a reason you are using this format since it is not a normal
percentage format, the format you are using cannot display decimals if there
aren't any
Change it to

=TEXT(Data!A10,"0.00%")

instead

Signature

Regards,

Peo Sjoblom

> Hi.
>
[quoted text clipped - 16 lines]
>
> Thanks.

Reply to this Message

Rate this thread: