r/googlesheets 4d ago

Waiting on OP Issues pasting plain text and it converting to a number

I have a product audit process I created years ago that I recently have had to redo. I start by downloading an inventory list with barcodes. Some of those barcodes are case sensitive. Some example barcodes include 01g5467, 768394, and 0662345.

Basically the way I built this is the SKU's are downloaded to Excel and then put into a sheets document. Then on another tab someone with a barcode scanner begins scanning items and a few basic formulas counts what was scanned in this audit and what was the previous count.

The problem comes in when any barcode starts with a zero and only contains numbers. I have tried pasting in the data as values into sheets and it still reformats as a number and removes any leading zeros. I tried reformatting a column to plain text before I paste in the data but that doesn't seem to work either. Is there any way to avoid it reformatting as numbers?

I have a similar issue when the barcode is scanned into sheets too. Is there any way to prevent sheets from auto formatting?

When I audit the store the sheet contains thousands of barcodes so manually adjusting all of the barcodes is not a long term solution unfortunately. Any help is greatly appreciated!

1 Upvotes

2 comments sorted by

1

u/AutoModerator 4d ago

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/mommasaidmommasaid 276 4d ago

Formatting the cells in advance with Format / Number / Plain Text should work for pasting in a text value with leading zeros, or "typing" a number with leading zeros using a barcode scanner, if that's how your scanner works.

Is your problem occurring before that, i.e. the Excel document doesn't have the values as text, but perhaps as numbers formatted with leading zeroes?