cell will not change

A

Alyssa

The cell is currently showing a single digit, eg. 4. I want it to display
$4.00. However when I format the cell to currency with 2 decimal places -
nothing happens. What am I doing wrong?
 
P

Peo Sjoblom

The 4 is probably text, make sure there are no hidden characters, try

=LEN(A1)

where A1 is the cell with 4, if it returns 1, then copy an empty cell,
select A1 and do editt>paste special and select add, now try to format the
cell as currency


Regards,

Peo Sjoblom
 
M

Max

Alyssa said:
The cell is currently showing a single digit, eg. 4. I want it to display
$4.00. However when I format the cell to currency with 2 decimal places -
nothing happens. What am I doing wrong?
Click to expand...

The cell's value is probably a text number. If the cell's value is an entry,
just copy an empty cell, then do a paste special > add > OK to coerce it to a
real number (we're just adding a zero here in this op). The formatting should
work fine now.

And if it's a formula in the cell (not an entry), eg: =LEFT(A2,1), just add
a zero to it, ie make it as: =LEFT(A2,1)+0. Adding a zero here will likewise
coerce the text number returned by the formula's LEFT(A2,1) to a real number
without impacting the numeric value returned.
 
G

Gord Dibben

Alyssa

Could be the 4 is text formatted.

A check would be enter in an adjacent cell =ISNUMBER(cellref)

True or False?

If False, re-format to General then copy an empty cell and paste special onto
the cell with your "digit"


Gord Dibben MS Excel MVP
 
A

Alyssa

Hi Max - I did everything you suggested and it still wouldn't let me change
it. Do you have any other suggestions?
 
A

Alyssa

Hi Peo - when I entered the formula you suggested it did not return 1 and
simply blue outlines the cell in question when the formula is selected. Any
other suggestions?
 
A

Alyssa

Hi Gord - when I entered the formula you suggested it did not return a true
or false answer and simply blue outlines the cell in question when the
formula is highlighted. Any other suggestions?
 
M

Max

Alyssa said:
Hi Max - I did everything you suggested and it still wouldn't let me change
it. Do you have any other suggestions?
Click to expand...

From your responses given to Peo and Gord, perhaps the entire sheet may have
been formatted as text. Try selecting the entire sheet, then click Format >
Cells > General > OK. Then try again the original suggestions given. Note
that you need to re-confirm the formulas suggested (eg click inside the
formula bar, then press ENTER) before the formulas will work. Merely
re-formatting the sheet to General (from Text) will not fire the formulas.
 
A

Alyssa

Hi Max - I've tried everything you've suggested including reformatting the
whole sheet. It seems that here are no 'normal' cells anywhere in the sheet
as the =LEN(cellref) formula simply displays exactly as typed no matter how
many times I hit the enter button from within that cell. I did try this
formula in a new sheet and it returned an answer no problem. Could it be that
for some reason the whole worksheet has been infected?
 
M

Max

Alyssa said:
Hi Max - I've tried everything you've suggested including reformatting the
whole sheet. It seems that here are no 'normal' cells anywhere in the sheet
as the =LEN(cellref) formula simply displays exactly as typed no matter how
many times I hit the enter button from within that cell. I did try this
formula in a new sheet and it returned an answer no problem. Could it be that
for some reason the whole worksheet has been infected?
Click to expand...

unlikely .. but you could always scan the file with an anti-virus ..

What happens if you do an entire sheet copy (the problematic one), then
paste special as values into a new sheet? Then carry out the earlier
suggestions in the new sheet. Are you able to format the stubborn "4" in the
cell?
 
G

Gord Dibben

Hit CTRL + `(backquote above Tab key)

Gord

Hi Gord - when I entered the formula you suggested it did not return a true
or false answer and simply blue outlines the cell in question when the
formula is highlighted. Any other suggestions?
Click to expand...

Gord Dibben MS Excel MVP
 
Top