Format cell to display numbers instaed of ###'s

  • Thread starter Thread starter DavidJ726
  • Start date Start date
D

DavidJ726

Not sure how to querry this in the KB.... but I want to be able to have a
number such as 4 1/8 in a cell that is set at width less than what is
required to display it properly. Same with a date/time entry. In cells
where I'm entering text, the text will "spill" over if the adjoining cells
are empty of data. No so with number's.

How do I change it so it will display as 4 1/8 without widening the cell?
If
I place an apostrophe ' in front of the number, or format the cell as plain
text it will work, but I really need to retain the numeric value.

Thanks,

David...
 
David,

I'm not too sure what you're asking. I think you already know how to format
fractions so they appear as fractions (Format - Cells - Number - Fraction).
But numbers, if they don't entirely fit in the cell, are deliberately shown
as ###, rather than show a partial number, which would convey the wrong
value. What exactly do you want it to do if it doesn't fit? Lop off part of
it? Spill into the next cell? It won't do any of these things with
numbers.

One possibility is to format the cell for text, or use the apostrophe as you
did, then in any formula that refers to it, use VALUE(cell) to convert it
back to a number. Actually, this isn't necessary in some situations, as
Excel will coerce it to a number as necessary.
 
Not sure how to querry this in the KB.... but I want to be able to have a
number such as 4 1/8 in a cell that is set at width less than what is
required to display it properly. Same with a date/time entry. In cells
where I'm entering text, the text will "spill" over if the adjoining cells
are empty of data. No so with number's.

How do I change it so it will display as 4 1/8 without widening the cell?
If
I place an apostrophe ' in front of the number, or format the cell as plain
text it will work, but I really need to retain the numeric value.
Click to expand...

The only way I know to do that, retaining the numeric feature, is to use
Format/Cells/Alignment Shrink to Fit.

Making it text -- some but not all Excel functions and operators may translate
it automatically, but you may not be able to count on that.


--ron
 
Earl,

You are correct, I have the cells properly formated, but I'm working in a
cell that is 25 pixels wide and the cell needs to be 40+ wide in order to
display various values correctly. What I want / need is to spill over into
the next cell without (hopefully) converting it to a text value. However,
if I understand you correctly, (and I'm not familiar on how to use
VALUE(cell)) I can format the cell as we've described, and in a cell where
calculations are being done, I can somehow force it to treat that cell as a
numerical value...?

My situation; Column P which is set to 25 pixels, is where the numbers I'm
entering are displayed as ###'s. The values in P6 & P7 are the before &
after weights of a product, and R7 would show the difference as a
percentage. So I want the values of P6 & P7 to spill across into Q6 & Q7.
Maybe the easist way to accomplish this is resizing some columns, which is
what I didn't want to do... <sigh>

I didn't realize numbers couldn't spill over. I guess I always did column
width adjustments because my needs allowed it...

Thanks,

David...
 
David,

In many cases, the cell, even though text, will be coerced into numeric in a
formula if seen with mathematical operators, e.g.: =P7-P6. If it didn't
want to do that, you'd write =VALUE(P7) - VALUE(P6). Now you can either
format the cell in advance as text, or precede it with the apostrophe. In
either case, it can spill into the next cell, and still be used as numbers
in formulas.
 
Back
Top