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

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...



0
DavidJ726 (42)
9/4/2004 2:31:59 AM
excel 39879 articles. 2 followers. Follow

4 Replies
983 Views

Similar Articles

[PageSpeed] 3

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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"DavidJ726" <DavidJ726@hasnoaddress.com> wrote in message
news:OG51VdikEHA.644@tk2msftngp13.phx.gbl...
> 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...
>
>
>


0
nothanks4548 (968)
9/4/2004 2:44:13 AM
On Fri, 3 Sep 2004 19:31:59 -0700, "DavidJ726" <DavidJ726@hasnoaddress.com>
wrote:

>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.
>

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
0
ronrosenfeld (3122)
9/4/2004 2:57:09 AM
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...


"Earl Kiosterud" <nothanks@nospam.com> wrote in message
news:uxR9RkikEHA.272@tk2msftngp13.phx.gbl...
> 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.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "DavidJ726" <DavidJ726@hasnoaddress.com> wrote in message
> news:OG51VdikEHA.644@tk2msftngp13.phx.gbl...
>> 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...
>>
>>
>>
>
>



0
DavidJ726 (42)
9/4/2004 3:48:10 AM
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.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"DavidJ726" <DavidJ726@hasnoaddress.com> wrote in message
news:ec2f8HjkEHA.3476@tk2msftngp13.phx.gbl...
> 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...
>
>
> "Earl Kiosterud" <nothanks@nospam.com> wrote in message
> news:uxR9RkikEHA.272@tk2msftngp13.phx.gbl...
> > 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.
> >
> > -- 
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "DavidJ726" <DavidJ726@hasnoaddress.com> wrote in message
> > news:OG51VdikEHA.644@tk2msftngp13.phx.gbl...
> >> 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...
> >>
> >>
> >>
> >
> >
>
>
>


0
nothanks4548 (968)
9/4/2004 4:22:09 AM
Reply:

Similar Artilces:

Cells formatted for general and text show scientific format
I am working on an Amazon.com shipping confirmation upload. The workbook is downloaded from amazon.com. When I enter the Order Item ID number which is 14 characters, it displays in scientific format. I have tried general, number and text formats. Number format works if there is no leading zero but general and text do not. - Are you formatting the column for Order Item ID number before entering the data? That should solve the problem. - If you are formatting after entering the data, selecting F2 (Edit) in a cell will then put that cell into the correct text format (except for leedi...

Excel Formatted Spreadsheet To SQL ?
Hello, Is there any reasonably straightforward way to convert an Excel spreadsheet, or part of one, to a format that SQL would accept ? Thanks, Bob Usually the method that works is to save the worksheet in CSV format. Then import the CSV format into your database. Access will import CSV and so wil a number of other databases. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196450 http://www.the...

Multiplying the number in one cell and providing the product in an
I've never set up a formula before, not even the most basic. I need help and I'm not sure if its entirely possible. Please let me know. Labor Hours Labor Cost (95/hr) 2 190 5 950 I need to be able to enter in the number of hours in one cell (under labor hrs) and have those hours multiplied by the cost ($95) and the product displayed in the cooresponding cell (under labor cost). Is this possible? If so, how? EXCEL 2007 1. In cell A2 I have put your 2 (your hours figure). 2. In cell B2 I have put ...

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

series of cells in a column #2
thanhyou so vey much -- ROLG ------------------------------------------------------------------------ ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16066 View this thread: http://www.excelforum.com/showthread.php?threadid=275347 ...

Combine the contents of two cells.
I want to copy the contents of a cell and insert it at the beginning of the text of another cell: ie I want to combine the text contained in two cells into one cell. I can do it manually but would like to use a macro as I have a lot of cutting and inserting to do!! I use Office 2000. Hi! Assume you want to combine cells A1:A100 with cells B1:B100. In cell C1 enter this formula: =A1&B1 If you want a space between values: =A1&" "&B1 Drag copy down to C100 or just double click the fill handle. Now, if you no longer need the data in columns A and B select C1:C10...

Chaging the display font size
Hello, How can I change, within my code, the display font size from 96 DPI to 120 DPI? I would like to do it using either MFC or the Win32 SDK. Thanks, Eitan ...

Count of cells since first purchase
Hi, Looking for a bit of help with the below. I want to calculate how many weeks have passed since a customer first purchased from me. So for example see below. I want to calculate in cell E2 how many weeks have passed since Account 1 first purchased from me. In cell E3 i want to calculate how many weeks it is since Account 2 first purchased from me. Week 3 April Week 4 April Week 1 May 10 Account 1 2 3 Account 2 1 Account 3 ...

Cells that "appear" empty plot as zero. Do not want cells to plot.
I read thru some of the posts about plotting empty cells and can't seem to find the right answer to my problem. I have a bar chart (Jan thru Dec) which I currently show data running through June. All cells contain the same formula (therefore none are "really" empty). My formula uses the " " to make the cells for July thru Dec "appear" empty, therefore the graph is plotting these cells as zero. I know that I could use 'NA()' and force a #NA error, but I don't want that error message to appear in my cells. Is there a way to not get the ...

Conditional Formatting with Text
Hei gurus, How can I tell CF to turn a cell green if the cells contain text "great" and red if the cells contain "unacceptable??" TX a great deal cell value>="great">format>patterns (choose green) add cell value>="great">format>patterns (choose red) >-----Original Message----- >Hei gurus, > >How can I tell CF to turn a cell green if the cells >contain text "great" and red if the cells >contain "unacceptable??" > >TX a great deal >. > Select the range of cells Format > Co...

How do I set a cell value based on a formula in another cell?
How do I set a cell value based on a formula in another cell? =IF(D4="P.B.A.",G8,0) ,This works to set the value in the cell the formula is in, say cell A1. I would like to have this formula reside in cell A1 but set the value in cell A2 to (G8,0). Also it would be helpful if I could lock cell A2 based on a False condition from this formula in cell A1. Thanks Gary I note that this entry is also asked and answered in Programming. dingy101 Wrote: > How do I set a cell value based on a formula in another cell? > > > =IF(D4="P.B.A.",G8,0) ,This w...

In-cell progress bars?
Is there a way to have an Excel (2003) cell display a percentage result from a calculation as a shaded percentage of a cell instead of returning a result? It seems like that sort of thing should be in Format Cells... > Shading, but there's nothing there. This is possible to set up in an HTML cell by including a 1-pixel-wide graphic then changing the width attribute of the img tag. Is there anything similar in Excel? Thanks, -j One way you could do this is by means of this formula: =REPT("|",100*A1) if you have a percentage in A1 - it will repeat the pipe character once fo...

change format of existing numbers
I have a worksheet with thousands of phone numbers on it, but not formatted as phone numbers. If I change the existing numbers' cell format to a custom ###-###-#### format, it won't format the numbers this way unless I double click in each cell (cell content edit mode) and press enter. Unlike regular number formats, it seems as though excel won't convert existing data to my custom format unless: -Cell is formatted and THEN number is entered -If cell has existing number, I must click INTO the cell (editing mode) Please help! much thanks mari Let me add to my ques...

Locking Cells #6
Is there any way to lock cells in excel so that no body can overwrite it, specially cells containing formulas. TIA Try the instructions here: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "newbie" <jcd@refid.com> wrote in message news:O0DJJeihFHA.1248@TK2MSFTNGP12.phx.gbl... > Is there any way to lock cells in excel so that no body can overwrite it, > specially cells containing formulas. > TIA > > ...

Numbering a spreadsheet
Hello Excel Experts! I have a spreadsheet that is numbered 1-105, but because of column headers the numbers in my spreadsheet do not coincide with the excel row numbers. Oftentimes, I have to delete rows, but I want the original numbering to stay intact. For example, today, if I delete the row I've numbered as 35, I have to go through and re-number rows 36-105. Is there anyway to avoid manually re-entering the information so that it automatically corrects itself when a row is deleted. Look here: http://j-walk.com/ss/excel/usertips/tip035.htm -- Greeting from the Gulf Coast! http://my...

prevent Excel from EVER automatically converting numbers to dates
in Excel v. X for Mac how do I permanently prevent Excel from EVER automatically converting numbers to dates. Formatting individual cells as text will not work in my applications. I need to disable this funtion. Not possible - there's no preference option to turn this off. Instead of preformatting as text, entries can be made with an apostrophe as a prefix: '3/4 You might be able to use an event macro to change it back retroactively, but that depends on knowing what format the user entered it. For instance, 3/4, 3-4, 4Mar, mar4, etc will all be interpreted as 4 Marc...

How attach a file in one cell
I'd like to know if it'll be possible attach a file (word, pdf...) in one cell. I'm doing an Excel doc. and I need to put some instructions by doc. attached. in some cells. Thanks! In article <A23394A2-B804-4855-875A-7664B90F4F48@microsoft.com>, How attach a file in the cell <How attach a file in the cell@discussions.microsoft.com> wrote: > I'd like to know if it'll be possible attach a file (word, pdf...) in one > cell. I'm doing an Excel doc. and I need to put some instructions by doc. > attached. in some cells. Thanks! Cells can contain...

PLS HLP Waterfall doesn't display properly
I am following various instructions for creating a waterfall. I have supplier names in column A, Values in column B and an additional column C that will "disappear". I have followed the formula for column C so that it ends up with a 0 value in the last cell, then created a stacked column chart. My bars don't seem to float - they are at the bottom of the chart. I have tried different ways of rearranging, but to no avail, although I have been at this for many, many hours and maybe am just exhausted. I would like my largest segment to appear from 0, then the next largest v...

Help? My formula is displaying in my cell
Hi there - For some reason I have one cell (in a workbook where everything else i working) that when I type in the formula in the formula bar . . . th stupid formula is showing up in the cell. My formula is simply this: =Info!B13 Because I am taking text from a sheet labeled Info in cell B13. I'v done this for about a million cells in this workbook so far an everything else has worked. This one stupid cell is just showing m the formula. Can anyone give me some suggestions to fix that? Much appreciated! Thanks! Jennife -- Message posted from http://www.ExcelForum.com Hi Jennifer,...

sorting number with multiple minus signs.....
We try to sort a list with partnumbers. This list includes partnumbers starting with a minus sign and including an extra minus signs (like -12930-03), numbers not starting with a minussign but including multiple minussigns (like 15-6064-10) and "normal" numbers with numbers or characters. We want all numbers starting with a minussign to appear on top. But when we try to sort them, the partnumber starting with 0... are on top of the list and the ones with z... are at the bottom. When we format the cells as number, the partnumbers are sorted with -... on top and z... at the b...

Date Format #7
I want to display dates in the European format dd/mm/yy. When I enter dates they appear in the American format mm/dd/yy. Format Cells > Date in Excel only gives me the American format. Regional Options in Control Panel are set to English (Ireland) with the date format dd/mm/yy. How can I change the date to European format in EXCEL? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial...

Auto fill cell addresses by not 1
Hi folks, Is there a way to auto-increment the cell address in a formula by an increment other than 1. For example by 15: =Fubar(A1,A2) =Fubar(A16,A17) =Fubar(A31,A32) : : : I have rather complex formulas that point to data on another excel sheet where the data is organize into blocks (it is generated by another program) rather than in nice sequential columns. Having to manual enter the cell addresses for 200 rows is a PITA! Cheers, Peter How about a little work? Say I want the formula in row 1 (say E1). I'd put this formula in E1: ="$$$=Fubar(a" & (...

Excel and clear cells....
Hi.... I have this large spreadsheet showing 52 weeks, and for each week 10 areas not adjacent to each other, all of wich I need to be cleared every year. In between areas needed to be cleared there are some hidden cells, rows and columns. Is there a way to easily clear these areas, without clearing hidden and locked areas? -- --- rgs Zadig Galbaras (nick) www.tresfjording.com You can highlight disjointed areas of cells using the Ctrl key while you click and drag. For example, click cell A1 and drag to cell B2 to highlight 4 cells. Now hold down the Ctrl key and click cell A8 and...

Populate empty fields with a number
I have a a column which has 3 possibilites, 1,2 or 3. I have already populated fields with either a 1 or 2 based on another query. I wish to populate the remainder of fields which have no content with 3. I have tried to write the query as UPDATE dbase SET dbase.[field] = 3 WHERE dbase.[field] <> 1 AND <> 2. But this doesn't work, and have tried UPDATE dbase SET dbase.[field] = 3 WHERE dbase.[field] = "", and this doesn't work either. Data type mismatch occurs on last query. Any help would be great? Cheers -- Paul Wilson Paul, You might try: UPDATE dbas...

Hyperlink cell placement
This should be easy but I'm having a hard time with it. I have a large wooksheet and would like to be able to add Hyperlinks to it so that whenever I hit a hyperlink, the cell chosen is in the upper-left corner of my screen every time. What happens is the cell I enter for the Hyperlink goes to the nearest point on the screen and in not revealing the part of the page I want to view. This must be simple but I'm struggling with it. Hi Nick, Add this little bit of code to the sheet containing the hypelinks. i.e. open the VBE by pressing {Alt}{F11}, double-click the sheet, and past...