Microsoft Excel help needed
 

Not Ducati related but I'm trying to put a number into an Excel database with a "leading zero" I can do this as text but then cant use a numeric function on it. I'm trying to show 03050601 which is a date code with up 01 to 99 additional identifiers. I want to type in 03050601 into a cell then beneath that cell put =thatcell+1 then copy that down the list adding one at a time to the sequence.

Trouble is that if I select "number" as the format the leading zero dissapears when the number is typed in.

I'm sure I've done this successfully before but can't remember how.

HEEELLLPPPP !!

Put a ' before the zero to display the leading zero, then hover over the lower RH corner of the cell click & drag down with the LH mouse button for sequential numbers in the cells below

[Edited on 10-5-2006 by Wylie1]

That seems to work for the individual cell but then if I put the equation in the cell beneath the zero dissapears again.

Thanks for the reply Wylie

Oops, sorry... I was editing my reply after reading the rest of your post! See above

Right-Click the cell then pick "Custom" from the Category for the cell format.

In there it should automatically pick the format of "00000000" but you can type it in if needed.

In formatting, 0 is a forced digit (inc. leading zeros) and # is an optional number, so wouldn't include leading zeros.

Eg:

01234 as 00000 = 01234
01234 as ##### = 1234

Still seems to lose the leading zero ;-(((

God it must be exciting at work...................:lol::lol:

You may need to set the cell formatting on the range once you've copied the formulas over. Excel can be funny like this sometimes as it constantly tries to do a "best guess" about the formatting of a cell when the contents change. It's a right pita for us developers!

Haven't you got some paperwork to fill out? :D

Yeeehaaaa, that works just fine. Many thanks guys, great info and very quick as usual. Almost tempted to chuck my slide rule and four figure tables away now :lol:

Or some old dears with no lights on their bikes to go and nick :frog:

Spot on again; it tried to set the format to Greek for some of the cells !! All sorted now so I'm going to risk merging the database to my labels and inspection reports.

They make us do it in excel now.................any help? :lol::lol:

Probably not cos the zeros keep getting added at the other end when it comes to speeding fines :P:P:D

Spoke too soon saved the database then tried to merge and the leading zeroes dissapeared. Checked the format again and the whole column has reformatted to Greek. Arghhhhh!!! Bl**dy Microsoft !!!!*"+***

Only consolation is, it's still a much better piece of software than Word!

Paul, you need to include text in your formula and copy that formula to all of your cells.
e.g. in cell C8 I have entered 7425
I have formatted cell C8 to 6 0's (custom format)
in cell C9 I have entered a number 12 (no custom formatting)
in cell C10 I would normally have entered =C8*C9 and seen a result of 89100
Custom formatting cell C10 could produce 089100 with your leading zero's, but you could also use the following formula instead:
=TEXT(C9*C8,"000000")

Unfortunately that does turn the cell into text but you can still use that text in another formula e.g. =C10*1 = 89100

Merging into Word you will need to alter the field formatting withinthe word document - use F9 to reveal field codes

Thanks Alan, interesting option but what I'm trying to do is make up a simple column (which I can do if there are no leading zeros) so that I have a production date say 030406, with 01 to 99 behind that to give me up to 99 batches per day. Then by using the first entry in the column say 03040601 I can put a simple formula in the next row down =that cell+1. This when copied and pasted into the required number of cells gives a simple chronological record, when the date changes by a day it's a simple matter of changing one cell and carrying on copying the +1 formula. Once I've saved the excel sheet I can use the info in all sorts of ways by merging to word documents. Labels, production test records, delivery notes etc.

Just tried amending to suit my application and by formatting all the cells as text it seems to be working.

Thanks again to all for the advice

[Edited on 10-5-2006 by Paul James]

Paul, use of formatting can extend to dates as text.
I'll speak in excel cell terms
a table with three columns, starting at C5.
Cell C5 - enter the date, format the cell as custom with ddmmyy
C5 should now say 030406
Cell D5 - enter the number 1, format the cell as custom with 00
Cell D5 should now say 01
Formatting of these two cells is pretty irrelevant really because our formatting will take place in the result cell E5

Cell E5 - enter the following formula: =TEXT(C5,"ddmmyy")&TEXT(D5,"00")
Cell E5 should now contain the text 03040601

Should now be a case of just copying the relevant rows down as far as you need and adding your own formulas.
e,g,
Cells C6 to C100 enter formula =$C$5
Cell D6 enter = D5+1
And just drag down the E cell formulas.

If you get stuck send us a u2u

Seems to be all working now so fingers crossed and thanks again for all the info, much appreciated.