Barcode Scanning in Excel not working correctly

[karkas]

I need to track medications I scan with barcodes like this

.

If I scan this barcode into Word or any text editor, I correctly get 010035107925501417150731103054734

If I scan it in excel I get 10035107925501400000000000000000

Anyone know what's wrong and how I can get excel to grab the whole number string including the final 18 numbers?

Thanks in advance

 

[thatoneguy650]

Excel only does 15 digits for numbers. It is seeing that big long barcode as a number and can't display the other digits past the first 15. Word is seeing it as text and can display all of the "numbers" as a string. You are going to have to format it as text in excel before you paste it or add an apostrophe before the number to force excel to format as text.

 

[karkas]

Thanks, that makes sense (though, I don't understand why excel would be limited in this way)

Could I at least capture the text... Is there a way to convert text back to numbers? If so, can this be done automatically?

 

[thatoneguy650]

If you try to convert it to a number in excel you will lose the digits again. Even though a bar code is a series of numbers, it doesn't need to be treated as a number in excel as you wont be manipulating it like a number (sums, averages, maximums,etc.). It will work as text for a vlookup/IndexMatch which is mostly what bar codes are used for.

 

[karkas]

If you try to convert it to a number in excel you will lose the digits again. Even though a bar code is a series of numbers, it doesn't need to be treated as a number in excel as you wont be manipulating it like a number (sums, averages, maximums,etc.). It will work as text for a vlookup/IndexMatch which is mostly what bar codes are used for.

I never realized excel had a 15 number limit. Unfortunately, in my case I DO need to manipulate these numbers, because they actually do yield data (expiration dates, lot numbers, etc).

Can I somehow break it up to split across three cells? (33 digits need recorded and manipulated)

 

[thatoneguy650]

I never realized excel had a 15 number limit. Unfortunately, in my case I DO need to manipulate these numbers, because they actually do yield data (expiration dates, lot numbers, etc).

Can I somehow break it up to split across three cells? (33 digits need recorded and manipulated)

You can still extract parts of the barcode and use those as numbers. Extract the numbers you need then use the VALUE() function to turn them back into numbers. Here's an example, A is Text, B is a number, and C is a date

Excel Workbook
	A	B	C
1	SKU	Lot	Expiration Date
2	010035107925501417150731103054734	3054734	7/1/2015
Sheet1

 

[karkas]

You can still extract parts of the barcode and use those as numbers. Extract the numbers you need then use the VALUE() function to turn them back into numbers. Here's an example, A is Text, B is a number, and C is a date

Excel Workbook
	A	B	C
1	SKU	Lot	Expiration Date
2	010035107925501417150731103054734	3054734	7/1/2015
Sheet1

Thanks! I'll need to slice and dice some more, but that should help a lot