Smartsheet adding an apostrophe to the beginning of a scanned barcode number

Carlee Schiffner

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.

ker9

@Leibel S - I could not get your formula to work but I got this to work:

@Carlee Schiffner

=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)))

Leibel S

Try using a Substitute

Carlee Schiffner

Using Substitute to eliminate the apostrophe does not seem to do the trick.

Leibel S

Can you elaborate on your setup?

Carlee Schiffner

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.

Leibel S

What is in the #NOMATCH formula?

Carlee Schiffner

=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.

Leibel S

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))

Carlee Schiffner

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.

ker9

Hi @Carlee Schiffner

@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)

Carlee Schiffner

Thank you @ker9, I will give this solution a try!

Leibel S

@Carlee Schiffner

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))

ker9

@Leibel S - I could not get your formula to work but I got this to work:

@Carlee Schiffner

=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)))

marc4

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

Carlee Schiffner

@ker9 using a substitute column to eliminate the apostrophe then a helper column is working! Thank you for all your help.

Carlee Schiffner

@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.