Custom Format

  • Thread starter Thread starter Nicole
  • Start date Start date
N

Nicole

I tried to apply custom format #,##0.00 on cells in a
worksheet in Excel 97, however, the format won't change.
It is always displaying the same old number Format.

Any ideas what why that is?

Kind regards

Nicole
 
Nicole said:
I tried to apply custom format #,##0.00 on cells in a
worksheet in Excel 97, however, the format won't change.
It is always displaying the same old number Format.

Any ideas what why that is?

Kind regards

Nicole
Click to expand...

It works fine for me on my Excel97. What format were you using before? Can
you give an example or two of the actual numbers (as shown in the formula
bar) and how they are displayed?

Are you sure they are really numbers and not text? You can check this (for
example, for A1) with a formula such as
=ISTEXT(A1)
which will return FALSE if A1 is really a number.
 
It works fine for me on my Excel97. What format were you
using before? Can
you give an example or two of the actual numbers (as shown in the formula
bar) and how they are displayed?

Are you sure they are really numbers and not text? You can check this (for
example, for A1) with a formula such as
=ISTEXT(A1)
which will return FALSE if A1 is really a number.
Click to expand...

Thanks for your reply.

Even when I open a new worksheet and try to change e.g.
1.234,56 to 1,234.56 it won't work. It also won't apply
any of the other formats so my guess is that something is
wrong with the software only that my colleague has the
same problem. We finally cheated and found a way around.
First we replaced , with ! then replaced . with , and
finally replaced ! with .

I know not neat...but the only way we could think of :)
 
Nicole said:
Thanks for your reply.

Even when I open a new worksheet and try to change e.g.
1.234,56 to 1,234.56 it won't work. It also won't apply
any of the other formats so my guess is that something is
wrong with the software only that my colleague has the
same problem. We finally cheated and found a way around.
First we replaced , with ! then replaced . with , and
finally replaced ! with .

I know not neat...but the only way we could think of :)
Click to expand...

What you had, then, was not a number but a text string, which you would have
found using =ISTEXT(A1). There is nothing wrong with your software.

A number can only contain the numeric characters 0 to 9 and whatever is set
on a particular PC as the 'decimal separator' (that is "." in the UK and US
or "," in continental Europe). If you actually type an entry containing both
"." and "," it will be interpreted as text. You cannot apply custom formats
to text.

When you apply custom formats to numbers, you are not actually changing the
number, merely the way it is displayed. Type in a number such as 1234.56 and
then try applying different number formats. You will see that you can make
it display as 1,234.56 or 1235 or 001235 or 1,234.5600 or £1,234.56 or
$1234.56 (amongst many others). But in each case the number in the cell
(which you can see in the formula bar) is unchanged. It's well worth
understanding this!
 
Back
Top