apostrophe problem

Mike matthys
Mike matthys ✭✭
edited 12/10/21 in Smartsheet Basics

Morning everyone, here is the problem

We use a form to scan barcodes, the box we scan has 2 barcodes, one or the other has to be scanned, and as it needs to be dummy proof both need to be handled.

for example K123456 and 123456 so the numbers are the same but one is without a letter in front

The barcode with only numbers who are added in the form (123456) gets the apostrophe infront, the once with K don't.

On the form we need to use the no validation option instead of setting the validation to numbers only, because we need the option to scan and that option doesnt appear when restriction is set to numbers only

So i use no restriction and now im trying to deal with the apostrophe's

I tried alot of different things to get rid of the apostrophe but nothing seems to work, i mean i can set a helper and use a mid() or a isnumber() ... but still when im trying to use the helper data in my formulas they exclude the data from that field in the formula

a simple join collect only shows me boxes scanned with a K infront

=IFERROR(JOIN(COLLECT(Doosnr:Doosnr; Datum:Datum; Datum@row; Methode:Methode; Methode@row; DubbeleDoos:DubbeleDoos; 1); " - "); "")

Doosnr is the helper

any ideas ? why isnt there a way to show the scan option when restrictions is set to numbers only ?


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Mike matthys

    Barcodes are scanned into Smartsheet as text, which is why when there are only numerical values in the code it adds an apostrophe at the front, to ensure that the number is input as a text string.

    You can strip this out by using the VALUE function in a helper column, like so:

    =VALUE([Barcode Column]@row)



  • yeah i tried that but trying to put the stripped string to a value gives issues with formulas

    for example idid the =value() in a helper field en used that helper field to fill up a join, everything thats started with a k showed up, everything that original starts with a ' doesnt show up

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Mike matthys

    Would you be able to provide a screen capture of what you're describing? A JOIN formula should join values together, regardless of the apostrophe or not. It sounds like perhaps one of your other criteria in the JOIN formula may be filtering out these rows, is that a possibility?