I am trying to type in and populate a list of consecutive numbers for animal eartags. However all the numbers start with a zero and it appears to be impossible to enter a number into an Excel (or Open Office) spreadsheet that begins with a zero. This number is essential though so there must be a way of getting it to stay there.

Any ideas?

EG tag number is 08607240790045
if I type that number in all I get is 8607240790045

sure there is a way to get the zero to stick?

Yep – format that cell as text (Format cell -> Number -> text)

Hmmm I had tried that. It doesn’t seem to work though. Should say, the numbers have to be then imported into a database so will need to behave as numbers (not sure if that is relevant though to the issue here)

Type a single quote, ie a ‘ before you type the number in

Sorry that won’t work as you want them to behave as numbers later in the database

What I think you need to do is type them in with a quote so you can see the zero – then use a formula in another cell to turn them into numbers so you can import those into the database, give me a second

ah this game…. yep text is one answer though it may cause you problems later on (played this game with my dad and his cattle tracking)

Instructions here;

https://support.office.com/en-gb/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7

When I say it doesn’t work. I can get the number to stay up with the zero at the start but I cannot then drag that number down to populate a list of 500 consecutive numbers

Type the numbers in with a quote ‘ (this forces the input to be treated as text)

then convert with VALUE( ) function

EG in cell A1 type ‘01234 in cell B1 type =VALUE(A1) … use the B1 cell to import to database.

When I say it doesn’t work. I can get the number to stay up with the zero at the start but I cannot then drag that number down to populate a list of 500 consecutive numbers

😀
Classic creeping functionality in an IT project specification – ask for one thing then expand requirements .. then complain that it doesn’t work / contract price has gone up !

Do NOT use tag numbers starting with a zero = problem solved 😉

5 posts from me in a row – where’s Junkyard ?

Make a new column with:

=”0″&(your_cell)

Then drag down?

jambalaya – Member
Do NOT use tag numbers starting with a zero = problem solved

You don’t know who you are dealing with….

the numbers have to be then imported into a database so will need to behave as numbers

The database field will have to be text as if it’s a number format it will ignore the leading 0.

one way would be to

in cell A1

=CONCATENATE(“0”,B1)

Cell A1 formatted as text

In cell B1 8607240790045

fill down cells A1 & B1

Are they all the same length?
If so select the column you are working on, select format cells and in the number tab you have a list of cell options.

Select ‘special’ and where there is a text box marked type: to the right, type in the number length as zeros (so for your example type 14 0’s) then whatever number you put in there will extend the leading zero’s to make it 14 long. If you were to drag down sequential numbers it puts the leading zeros in for you.

Have to do this all the time with our barcode numbers, works fine. A database will import them fine but won’t always take those leading zero’s into account though, so you might have to do a similar thing with that field to show them again if required. Easy enough in access, not sure on others.

Edit: img below shows where to go if you’re not familiar with excel and you can see it working if you look at the cell and cell entry field.

Use Custom Format “0”#
Put 08607240790045 in the first row, 08607240790046 in the second. Select both and drag down to fill rows.

What @T100 says will work for what you want as will @slow’s solution but might be a bit complicated to do if you are not familiar with custom formatting

as will @slow’s solution but might be a bit complicated to do if you are not familiar with custom formatting

However if he follows the instructions I linked to (and ignores some of the waffle on there) then it’s pretty straightforward 🙂

WOW, I thought there would be some EXCEL love on here and you didn’t dissapoint. Thanks for all the help guys. With a combination of your prompting and a bit of Googling I found a solution. Basically in Format >cell>number I clicked leading zeros back to zero and then forward again to 1. This gave a zero in the box next to it. I then typed # after the zero 15 times and it allowed me to keep the 16 figure number with a zero in front and I could drag down to populate the list. I have now sucessfully imported it to my database and it all matched the official eartag numbers so off to scan the lambs and see if there are any issues.

Thanks again for all the halp. This place is a veritable wikipedia of knowledge. 🙂

All’s wool that ends wool.

I see what you did there Cokie 🙂

Where is the “slaps forehead” emoticon

so off to scan the lambs and see if there are any issues.

So all your sheep have baa codes?

Sorry, I’ll get my coat…….

When I say it doesn’t work. I can get the number to stay up with the zero at the start but I cannot then drag that number down to populate a list of 500 consecutive numbers

Classic creeping functionality in an IT project specification – ask for one thing then expand requirements .. then complain that it doesn’t work / contract price has gone up ![/quote]

😆 😆 😆 😆