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

Options
Carlee Schiffner
Carlee Schiffner ✭✭✭
edited 03/25/24 in Smartsheet Basics

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

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 03/18/24
    Options

    Try using a Substitute

  • Carlee Schiffner
    Options

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    Can you elaborate on your setup?

  • Carlee Schiffner
    Options

    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
    Leibel S ✭✭✭✭✭✭
    Options

    What is in the #NOMATCH formula?

  • Carlee Schiffner
    Options

    =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
    Leibel S ✭✭✭✭✭✭
    Options

    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
    Options

    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
    ker9 ✭✭✭✭✭✭
    edited 03/25/24
    Options

    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
    Options

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @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
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    marc4 ✭✭✭✭
    Options

    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
    Options

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

  • Carlee Schiffner
    Options

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