Smartsheet adding an apostrophe to the beginning of a scanned barcode number
I am looking for a solution for the leading apostrophe Smartsheet is adding to numbers in a couple of sheets.
We are using several sheets to track the inventory of parts moving in and out of our shop. The sheet users are scanning a barcode into a form. The barcodes that are only numbers have an apostrophe added to the beginning. The apostrophe is causing cross reference formulas to get a "#NO MATCH" interrupting inventory counts.
It is worth noting that I cannot use a =VALUE() formula for the barcodes because some include text and numbers.
Looking for suggestions on how to eliminate the leading apostrophe! Thank you in advance.
Best Answer
-
@Leibel S - I could not get your formula to work but I got this to work:
=IFERROR(INDEX({Hose Inventory and Price List Range 1}, MATCH(VALUE([Misc. Bigge Product Number]@row), {Hose Inventory and Price List Range 1 bigge #}, 0)), =INDEX({Hose Inventory and Price List Range 1}, MATCH([Misc. Bigge Product Number]@row, {Hose Inventory and Price List Range 1 bigge #}, 0)))
Answers
-
Try using a Substitute
-
Using Substitute to eliminate the apostrophe does not seem to do the trick.
-
Can you elaborate on your setup?
-
Sure! I'll do my best to explain in text. I have several sheets all working together listed below:
-Inventory In + form
-Inventory out + form
-Archive
-Inventory and Price List
There are two sheets for tracking inventory moving in and out, one sheet for inventory coming in and one sheet for inventory moving out. There are forms on both sheets that are used to add rows to the sheets. Manufacturer barcodes are scanned into the forms. Some of the barcodes include text and numbers, and some only include numbers. (The number-only barcodes are the ones with the leading apostrophe Smartsheet is adding.)
I have the data entry from the inventory in and inventory out sheets merging onto one sheet as an archive to save the movement of inventory. In the archive, there is an INDEX/MATCH to show the corresponding internal company item number to the manufacturer's barcode.
The inventory and price list keeps an updated inventory amount by referencing the archive, subtracting inventory leaving the shop, and adding inventory coming into the shop. The inventory and price list utilizes our internal company item numbers.
The leading apostrophe Smartsheet is adding to the barcode numbers causes a #NO MATCH in the internal company item number column on the archive which leads to incorrect inventory counts on the inventory and price list.
-
What is in the #NOMATCH formula?
-
=INDEX({Hose Inventory and Price List Range 1}, MATCH([SUBSTITUTE]@row, {Hose Inventory and Price List Range 1 bigge #}, 0))
Range 1 is the internal company item number on the inventory and price list.
-
Try this:
=INDEX({Hose Inventory and Price List Range 1},MATCH(IFERROR(VALUE([Misc. Bigge Product Number]@row), [Misc. Bigge Product Number]@row), {Hose Inventory and Price List Range 1 bigge #}, 0))
-
I have tried to use VALUE in the past, but not all the data in the Misc. Bigge Product Number columns are values, some include text.
-
@Carlee Schiffner EDIT 2: In your helper column (Substitute), perhaps you can just use this formula:
=IFERROR(VALUE([Misc. Bigge Product Number]@row), [Misc. Bigge Product Number]@row)
Otherwise, more complex:
In your helper column (Substitute), you could remove the apostrophe and then use that column for your Index formula:
=IF(LEFT([Misc. Bigge Product Number], 1) = "'", RIGHT([Misc. Bigge Product Number]@row, LEN([Misc. Bigge Product Number]@row) - 1), [Misc. Bigge Product Number]@row)
Add another helper column to pull out the value from the first helper column (Substitute) and do the match on the results in the 2nd helper column:
=IFERROR(VALUE(Helper@row), [Misc. Bigge Product Number]@row)
Where Helper@row is your first helper column (Substitute)
-
Thank you @ker9, I will give this solution a try!
-
The formula I gave takes into account that some are text and not numbers. Did you try it?
=INDEX({Hose Inventory and Price List Range 1},MATCH(IFERROR(VALUE([Misc. Bigge Product Number]@row), [Misc. Bigge Product Number]@row), {Hose Inventory and Price List Range 1 bigge #}, 0))
-
@Leibel S - I could not get your formula to work but I got this to work:
=IFERROR(INDEX({Hose Inventory and Price List Range 1}, MATCH(VALUE([Misc. Bigge Product Number]@row), {Hose Inventory and Price List Range 1 bigge #}, 0)), =INDEX({Hose Inventory and Price List Range 1}, MATCH([Misc. Bigge Product Number]@row, {Hose Inventory and Price List Range 1 bigge #}, 0)))
-
Are you sure that the barcode reader is not adding the single apostrophe? The single aposrophe is an excel indicator to take the string as a literal. Most barcode readers have an option to add or not add the apostrophe.
/marc
-
@ker9 using a substitute column to eliminate the apostrophe then a helper column is working! Thank you for all your help.
-
@marc4 We are using the scan feature in the smartsheet mobile app to scan the barcodes. I spoke with smartsheet support and was told that the leading apostrophe is an expected behavior like it is expected in excel.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives