Entering a barcode without getting +12 formatted in

[robert_harper]

I'm trying to copy about 10,000 barcode over to a new spreadsheet, the prob is that when I copy them over they appear as XXXX+12 and then when I try and use them it doesn't like it. I can solve it if I format the column to text - and then double click it. This is ok for a hundred or so but fingers start to hurt after that! lol

[squiggler47]

Have you tried formatiing the column to text before you copy the barcodes there?

[robert_harper]

I did but it seems to get reformatted once I have pasted in the barcodes ...

[squiggler47]

Try Paste Special and select values from the list!

[robert_harper]

ah just noticed that it is copying from a spreadsheet that alreadys stores them as XXE+12 .... so can't paste it over like suggested. ... any other ideas?

Cheers

[squiggler47]

Just format the column as a number with 0 decimal places, no commas and widen the column so you can see all the digits!

As long as it contains less than 15 digits in the barcode it will display them all!

[robert_harper]

You have saved me hours!! lol that simple.

Thank you mate!!! brilliant phew lol

[robert_harper]

So close ... however now it is a number if the barcode has a zero infront of it as I've found out many do lol ... then it deletes the zero ( I understand that as it isn't of any value as a number ). But this is confusing my program and won't scan properly.

So I need to keep the zero infront whilst stopping the E+12 thingy

[GuruWannaB]

Let's say column A has your number (minus the starting 0's) and are supposed to be 15 digits long (if the starting 0 was there)

In column B put in

Please Login or Register  to view this content.

It looks to see if the length of the number is 14 and if so, puts in a 0 at the beginning...otherwise if its not...it just copies it over.

[martindwilson]

once its storred e+12 the leading 0 will disappear when you convert it back
to do them all at once use data text to columns/next/next select text ,click ok
then in another column use
=TEXT(A1,"00000000000000") with as many 0's as you need string length to be

[robert_harper]

So I can force the number of characters in the column and it would replace infront with zeros?

Only little prob is I have a DB with 20,000 barcodes some with shorter and some with longer, no set length.

Is there no way I could just ask excel not format anything and display everything then copy it all over.

I've also noticed mainly I think with CSV files but occassionaly the barcodes will all become the same code? Ever noticed this>??

Thank you

[martindwilson]

arnt you using standard length bar codes? 11 digits i think for upc

[robert_harper]

nah some are longer some are shorter not sure if the wholesaler create some or what but what about ... dreamt about this lol

If I/we created a visual basic macro type thingy to click each row in the column, then format the cell as text. I've found that if I manually click the cell with the barcode once it has been formatted to Text it will display the whole barcode - could this be done?

[martindwilson]

to do them all at once use data text to columns/next/next select text ,click ok

does just that

[robert_harper]

Erm... stupid question but what do you mean? How do I do this

[robert_harper]

Don't worry figured it out ... basically as you said Data - Text to columns - text - ok

Perfect!

Oh and again Thank you very much