Barcodes scanned into a webform not converting to a number automatically in the sheet!?

Hoping someone can help with this promptly as I haven't got an answer from my support ticket from the 19th. We have a grocery store and the floor team scan the barcode of the stock coming into the store into a form and select the qty and expiry date. This goes to a landing page which has a formula in several columns to look up the barcode in a product list and return the name and category etc. Once the #NO MATCH has gone and the barcode is matched to a product the row moves into a main register. However just in the last week or so the barcodes haven't been automatically converting to number format and the apostrophe that's usually hidden isn't hiding so I have to go into the sheet and back space the apostrophe and re-enter it for every submission so the look up formulas work!! 🤯

Why would this be and is it a glitch that can be fixed promptly?




  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭

    Some things to check:

    Has there been a change to the type of barcodes?

    What's the condition of the scanner lens? If it's dirty that can cause misreads.

    If it's not one of those things, you can make a change to your sheet. Create a new column to scan your barcode values into, and in the original barcode value column, use the VALUE function against the new column. Any numerical values showing up as text (the leading apostrophe) will be converted to numeric value.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • @Jeff Reisman Thanks for your reply. No, no change to the barcode type, still regular grocery barcodes as they have been for the last 12mths.

    Mostly just android devices that are being use, phones or tablet and it happens on numerous devices so wouldn't be a scanner related issue.

    I'm not really looking for a workaround as it has been functioning fine for 12mths and suddenly changed overnight.

    On further investigation, I've worked out that when scanning directly into the sheet on the app, using the in app scanner the numbers format correctly. However when scanning into a webform, using the in app scanner and submitting it to the sheet via the form it shows differently in the app to on the computer.. see images...

    On the computer it shows 2 apostrophes (note it is 2 apostrophes, not talking marks) so to correct it so the look up works I remove one of the apostrophes. On the app it shows only one apostrophe which to correct I have to backspace it and re-enter it. I don't even have to save it after removing it, literally backspace and put it back in.

    Appreciate if someone in the dev team can confirm its a bug and fix it asap!! MT!

  • I too have been encountering a lot of barcode scans coming in with multiple apostrophes, which the VALUE function will not filter out. This is causing a lot of manual editing. I agree with the Please Fix ASAP.


  • @Shaine Greenwood Sorry to rope you into this one but I noticed you first announced the in app barcode scanning on a post a few years ago so hoping you can help us here. There doesn't seem to be any response from my support ticket that I've followed up nor does there seem to be any live support options from smartsheet to report potential bugs, if that's what it is causing this issue? Would really appreciate your prompt and helpful response to this. We can have upto 300-400 items scanned in a week and we don't employee anyone to spend time doing the jobs the programs we pay for should do!!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    edited 04/28/22

    @Campus&Co. Illawarra Sorry I'm just now getting back to you, I missed the notification on your earlier comments. We may still find a workaround that works while waiting for bug resolution.

    Since VALUE won't help due to the two apostrophes, what about using SUBSTITUTE in your INDEX/MATCH formula to get rid of the apostrophes altogether?

    =INDEX({Name Lookup Sheet Name Range}, MATCH(SUBSTITUTE([email protected], "'", ""), {Name Lookup Sheet SKU range}, 0))

    The syntax is SUBSTITUTE( text to look in, text to find, replacement text, [number of instances to replace - optional] ). So in the above, for text to find I have a single apostrophe inside two quotes, and then just two quotes to replace it with nothing. The result is a plain text value of the number. If you want that to be a true numeric value, just wrap the SUBSTITUTE([email protected], "'", "") in a VALUE function: VALUE(SUBSTITUTE([email protected], "'", "")). The SUBSTITUTE formula as written gets rid of all apostrophes in the [email protected], no matter how many or where they are.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages