Help With Excel

Help With Excel

PostBy: sterling40man On: Thu Sep 01, 2011 7:54 pm

I'm running Microsoft Office 2010 and creating a chart in Excel with numbers. My problem is that when I enter a number like "08793456" and then tab to the next box (cell), the "0" disappears and I'm left with "8793456". I need to have that darn "0" there. I know it's something simple, but I just can't figure it out. Anyone know? Thanks.
sterling40man
 
Stoker Coal Boiler: Keystoker K6
Stove/Furnace Make: Keystoker
Stove/Furnace Model: KA-6

Re: Help With Excel

PostBy: lsayre On: Thu Sep 01, 2011 8:16 pm

The cell with the leading zero will have to be declared to be a "text" cell, instead of a number cell.
lsayre
 
Stoker Coal Boiler: AHS S130 Coal Gun
Coal Size/Type: Anthracite Pea
Other Heating: Resistance Boiler (if I ever get it fixed)

Re: Help With Excel

PostBy: rberq On: Thu Sep 01, 2011 8:27 pm

Using Excel 2003, I am able to define the column as text, enter the number with leading zeros, and then use it elsewhere in the spreadsheet as if it were a number.
For example, column A is defined as text. In cell A1 I type 00023, and because it is a text cell the zeros are not dropped.
Cell B1 contains the formula =A1 * 2, and displays as 46 (2 times 23).
Probably works the same way in Excel 2010, but I don't have 2010 on this computer, so I can't say for sure.


Edit: Oops. I see lsayre already gave the same answer while I was researching.
rberq
 
Hand Fed Coal Stove: DS Machine 1300
Coal Size/Type: Nut -- Kimmel/Blaschak/Reading
Other Heating: Oil hot water radiators, propane


Re: Help With Excel

PostBy: mozz On: Thu Sep 01, 2011 8:37 pm

Right click on the column that is giving you problems, format cells,number, treat as text.
mozz
 
Stoker Coal Boiler: Axeman Anderson 1982 AA-130 Steam

Re: Help With Excel

PostBy: rocketjeremy On: Thu Sep 01, 2011 8:46 pm

Easiest way that I usually accomplish this is to start the number with an apostrophe. For example instead of 08793456 type '08793456 and the zero should stay.
rocketjeremy
 
Stoker Coal Boiler: EFM DF-520
Coal Size/Type: Rice
Stove/Furnace Make: EFM
Stove/Furnace Model: DF520

Re: Help With Excel

PostBy: sterling40man On: Thu Sep 01, 2011 8:55 pm

Fixed! Thanks guys! :D
sterling40man
 
Stoker Coal Boiler: Keystoker K6
Stove/Furnace Make: Keystoker
Stove/Furnace Model: KA-6

Re: Help With Excel

PostBy: Freddy On: Fri Sep 02, 2011 4:54 am

Yaaa, I don't know much about Excel, but I'd guess start the number with a period. Of course that doesn't help if it ends up being a decimal point.
Freddy
 
Stoker Coal Boiler: Axeman Anderson 130 (pea)
Hot Air Coal Stoker Stove: Reading piece o' junk in the barn (rice)
Coal Size/Type: Pea size, Superior, deep mined

Re: Help With Excel

PostBy: WNY On: Fri Sep 02, 2011 6:15 am

Yes, a TICK Mark Or Single apostrophe will keep the formatting of the entered data with preceeding 0's. We have the same problem in our system at work, the Default Value is 0000.00 in the database, but when trying to update it from a spreadsheet, you have to enter '0000.00 . :)
WNY
 
Hot Air Coal Stoker Stove: Keystoker 90K, Leisure Line Hyfire I
Coal Size/Type: Rice
Stove/Furnace Make: Keystoker, LL & CoalTrol
Stove/Furnace Model: 90K, Hyfire I, VF3000 Soon

Re: Help With Excel

PostBy: Freddy On: Fri Sep 02, 2011 7:24 am

Don't some Excel programs do math for you? Does the tick bother in those setups?
Freddy
 
Stoker Coal Boiler: Axeman Anderson 130 (pea)
Hot Air Coal Stoker Stove: Reading piece o' junk in the barn (rice)
Coal Size/Type: Pea size, Superior, deep mined

Re: Help With Excel

PostBy: WNY On: Fri Sep 02, 2011 5:19 pm

It would calculate them if set to NUMERIC and NO LEADING Zeros.
But sometimes you need it for Zip Codes that start with Zero.

If it's a TEXT field, I don't think it will calculate it.
WNY
 
Hot Air Coal Stoker Stove: Keystoker 90K, Leisure Line Hyfire I
Coal Size/Type: Rice
Stove/Furnace Make: Keystoker, LL & CoalTrol
Stove/Furnace Model: 90K, Hyfire I, VF3000 Soon

Re: Help With Excel

PostBy: lsayre On: Fri Sep 02, 2011 7:38 pm

The leading apostrophe changes it to text.
lsayre
 
Stoker Coal Boiler: AHS S130 Coal Gun
Coal Size/Type: Anthracite Pea
Other Heating: Resistance Boiler (if I ever get it fixed)