Help With Excel

Post Reply
 
User avatar
sterling40man
Member
Posts: 1645
Joined: Sat. May. 03, 2008 11:52 am
Location: Northern Maine
Stoker Coal Boiler: Keystoker K6

Post by sterling40man » 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.

 
User avatar
lsayre
Member
Posts: 21781
Joined: Wed. Nov. 23, 2005 9:17 pm
Location: Ohio
Stoker Coal Boiler: AHS S130 Coal Gun
Coal Size/Type: Lehigh Anthracite Pea
Other Heating: Resistance Boiler (13.5 KW), ComfortMax 75

Post by lsayre » 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.

 
rberq
Member
Posts: 6445
Joined: Mon. Apr. 16, 2007 9:34 pm
Location: Central Maine
Hand Fed Coal Stove: DS Machine 1300 with hopper
Coal Size/Type: Blaschak Anthracite Nut
Other Heating: Oil hot water radiators (fuel oil); propane

Post by rberq » 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.

 
User avatar
mozz
Member
Posts: 1351
Joined: Mon. Sep. 17, 2007 5:27 pm
Location: Wayne county PA.
Stoker Coal Boiler: Axeman Anderson 1982 AA-130 Steam

Post by mozz » Thu. Sep. 01, 2011 8:37 pm

Right click on the column that is giving you problems, format cells,number, treat as text.


 
User avatar
rocketjeremy
Member
Posts: 176
Joined: Tue. Apr. 08, 2008 8:31 pm
Location: New Ringgold, PA
Stoker Coal Boiler: EFM DF-520
Hand Fed Coal Stove: Russo 1CWC
Coal Size/Type: Rice

Post by rocketjeremy » 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.

 
User avatar
sterling40man
Member
Posts: 1645
Joined: Sat. May. 03, 2008 11:52 am
Location: Northern Maine
Stoker Coal Boiler: Keystoker K6

Post by sterling40man » Thu. Sep. 01, 2011 8:55 pm

Fixed! Thanks guys! :D

 
User avatar
Freddy
Member
Posts: 7293
Joined: Fri. Apr. 11, 2008 2:54 pm
Location: Orrington, Maine
Stoker Coal Boiler: Axeman Anderson 130 (pea)
Coal Size/Type: Pea size, Superior, deep mined

Post by Freddy » 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.

 
User avatar
WNY
Member
Posts: 6307
Joined: Mon. Nov. 14, 2005 8:40 am
Location: Cuba, NY
Hot Air Coal Stoker Stove: Keystoker 90K, Leisure Line Hyfire I
Coal Size/Type: Rice
Contact:

Post by WNY » 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 . :)


 
User avatar
Freddy
Member
Posts: 7293
Joined: Fri. Apr. 11, 2008 2:54 pm
Location: Orrington, Maine
Stoker Coal Boiler: Axeman Anderson 130 (pea)
Coal Size/Type: Pea size, Superior, deep mined

Post by Freddy » Fri. Sep. 02, 2011 7:24 am

Don't some Excel programs do math for you? Does the tick bother in those setups?

 
User avatar
WNY
Member
Posts: 6307
Joined: Mon. Nov. 14, 2005 8:40 am
Location: Cuba, NY
Hot Air Coal Stoker Stove: Keystoker 90K, Leisure Line Hyfire I
Coal Size/Type: Rice
Contact:

Post by WNY » 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.

 
User avatar
lsayre
Member
Posts: 21781
Joined: Wed. Nov. 23, 2005 9:17 pm
Location: Ohio
Stoker Coal Boiler: AHS S130 Coal Gun
Coal Size/Type: Lehigh Anthracite Pea
Other Heating: Resistance Boiler (13.5 KW), ComfortMax 75

Post by lsayre » Fri. Sep. 02, 2011 7:38 pm

The leading apostrophe changes it to text.

Post Reply

Return to “Technology”