Stupid excel question...

Associate
Joined
19 Mar 2013
Posts
411
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.
 
Caporegime
Joined
18 Oct 2002
Posts
26,053
Changing a cell to text does exactly what you want. If the cell was set to be a number at the time you entered the data with a leading zero then it's gone forever though.
 
Man of Honour
Joined
19 Oct 2002
Posts
29,509
Location
Surrey
As above use text. I just tried putting a 13 character number into a text cell with a leading zero and it worked perfectly.
 
Associate
OP
Joined
19 Mar 2013
Posts
411
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.
 
Associate
OP
Joined
19 Mar 2013
Posts
411
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.
 
Associate
OP
Joined
19 Mar 2013
Posts
411
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.
 
Caporegime
Joined
18 Oct 2002
Posts
26,053
I see what you mean, it takes the current number value and retains it as text until you edit the underlying value.
 
Associate
OP
Joined
19 Mar 2013
Posts
411
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 :D
 
Soldato
Joined
8 Apr 2009
Posts
12,702
Format > Cells > Number > Custom > Type

Put 13 0's in there apply it to everywhere you want the cell formatted as you describe.
 
Associate
Joined
5 Jun 2013
Posts
1,531
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.
 
Associate
OP
Joined
19 Mar 2013
Posts
411
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.
 
Associate
Joined
27 Jan 2007
Posts
247
Location
Bucks
To try to fix the data you have already uploaded:
Select the column containing the barcodes
Data==>Text to Columns ==> Delimited, Tab (or some other character that will not exist in the column.
Then at the next step configure as Text

To import new data make sure that you use that same wizard from the outset. Precise instructions depend on your version of Excel and the format of the source data. But this should help: Data, Get External Data, From Text
 
Back
Top Bottom