Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
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 edittpaste special and select add, now try to format the cell as currency Regards, Peo Sjoblom "Alyssa" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
"Alyssa" wrote:
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? 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
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 On Thu, 20 Jul 2006 19:30:02 -0700, Alyssa wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
Hi Max - I did everything you suggested and it still wouldn't let me change
it. Do you have any other suggestions? "Max" wrote: "Alyssa" wrote: 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? 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
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? "Peo Sjoblom" wrote: 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 edittpaste special and select add, now try to format the cell as currency Regards, Peo Sjoblom "Alyssa" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
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? "Gord Dibben" wrote: 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 On Thu, 20 Jul 2006 19:30:02 -0700, Alyssa wrote: 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
"Alyssa" wrote:
Hi Max - I did everything you suggested and it still wouldn't let me change it. Do you have any other suggestions? 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
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? "Max" wrote: "Alyssa" wrote: Hi Max - I did everything you suggested and it still wouldn't let me change it. Do you have any other suggestions? 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
"Alyssa" wrote:
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? 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? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell will not change
Hit CTRL + `(backquote above Tab key)
Gord On Thu, 20 Jul 2006 21:49:02 -0700, Alyssa wrote: 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? "Gord Dibben" wrote: 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 On Thu, 20 Jul 2006 19:30:02 -0700, Alyssa wrote: 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? Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Cell Change Color - Need Help | New Users to Excel | |||
Cell references change when entering new data | New Users to Excel | |||
Look for change next blank cell in Range | Excel Worksheet Functions |