When I scan a barcode using a form, an extra ' is entered before the number.

Melissa Richardson

I´m using the barcode scan function in a form to enter the barcode. However, when I do the scan an extra character , ', is entered in front of the number. So instead of the number 10345470605 being entered, '10345470605 is entered instead. This is creating errors when I later do an INDEX/MATCH. How can I get rid of this extra ' ?

Paul Newcome

You are going to want to use a helper column that uses

=VALUE([Scan Number Column]@row)

Then you will want to reference this new column in your INDEX/MATCH.

SkiPatrolScott

@Paul Newcome Thanks for the help with duration,

on this issue, I am scanning Ski Passes to record skier violations or skier assists. The director needs to easily see multiple offenders. There are different bar codes in that some begin with a letter and some a number. ie EMP 12345 or 12345. The result is EMP 12345 (duplicates are recognized) but '12345(Duplicates not recognized). When I tried you Help Column, '12345 converted to 12345 but EMP 12345 became #invalid value. Any thoughts to being able to read both in the same column?

While I have you, I am using a find duplicate and it displays "DUPLICATE" is there a way to use the formula but display a RED symbol or RED FLAG from check box?

Paul Newcome

@SkiPatrolScott The reason this is not working for you is because you have text which throws an error in the VALUE function. Try using a SUBSTITUTE to replace the apostrophe with "" (double quotes).