Stupid excel question...

robin7 · 6 Aug 2015 at 17:54

Right I've been using excel to upload products for an epos system we're trialing in a shop I work at. I recently lost half my work as excel changed half the characters to 0's and I didn't notice. It said online it does this for cells with 15 or more numbers however the barcodes are 13 or less and it did it anyways.

So I've figured out I can change them to General format or Text and not lose the second half of the characters. However I do also lose the leading 0's found on some barcodes.

If I format it as text it changes a cell from 012345678910 to E+123HD01 etc rubbish.

I then have to double click on the cell to make it show as a number again.

Fine if I had a few products, but there are entire ranges (5k+ products) so I can't go through them all.

Is there another way of doing it?

If this doesn't make sense I can try and explain again. Literally had no idea excel could become this confusing.

I just want it to treat the cell as a number. Ie. I write in 0123456789 and it shows 0123456789. I don't want it to remove the leading 0's, and I don't want it to change the second half to 0's.... I just want it to show what I've entered. And I don't want to have to go through all the current ones clicking them to change them.

robin7 · 6 Aug 2015 at 17:58

Caged said:
Changing a cell to text does exactly what you want.

Changing it to text makes them all show as E+1235HC12 etc

I then have to double click on it, for it to then show it as numbers only. So I guess that's 'saving' the numbers correctly. But it's not showing it correctly and I'm worried our epos software won't accept that when I try to upload it.

robin7 · 6 Aug 2015 at 18:00

Hades said:
As above use text. I just tried putting a 13 character number into a text cell with a leading zero and it worked perfectly.

Try a 13 character number into a text cell with leading 0 in general or numerical format. Then change the format into Text and it changes what it shows you.

So while text will work for the products I add now, it would mean the few thousand I already have saved have to be redone I think? Well like I said when I just double click a cell that already has the format text, it then shows it correctly. I don't want to have to do this for all of them.

robin7 · 6 Aug 2015 at 18:01

Just checked. Format currently is Number. When I change the format of the column into Text it changes all my numbers. I have to double click each cell for it to show correctly.

robin7 · 6 Aug 2015 at 18:07

Caged said:
I see what you mean, it takes the current number value and retains it as text until you edit the underlying value.

Yup exactly. And with around 2k or so products entered so far... that's a lot of clicking! Ahhhh what a week it's been

robin7 · 10 Aug 2015 at 17:15

Phantom Shadow said:
If you want the leading 0's saved into the cell as well (rather than just displaying 0000000000123 where the cell actually still only saves 123) then use =TEXT(A1,"0000000000000"), then copy that and pastespecial as values into a cell you've already set as text.

Should be able to copy all your data out, get it in the right format with that formula, format where the data should be as text then copy back in as values and have the data exactly as you want it.

Not all have 13 characters though. It's between 9 and 13 I believe.

So frustrating haha.