Using JOIN-DISTINCT-COLLECT when values are not same data type


I am trying to create a string of values delimited with "+"s. It is part of a more complicated sheet, that matches Lot IDs to barcodes and other information from referencing another sheet. Essentially, I want the user to put in a lot ID (such as DXXXX) and the return to be a list of all the barcodes in the format:


I've used this formula:

="+" + JOIN(DISTINCT(COLLECT({Barcode}, {Lot ID}, [Enter Lot ID]1)), "+") + "+"

It usually works because the vast majority of the barcodes are numbers like 100102. However, a handful of barcodes have letters or other characters in them which causes the error #INVALID DATA TYPE.

My ideal goal would be to be able to list all barcodes in the format above, even when they have letters, but I don't think I can use the Distinct function with that at all. So, I would be okay if I could just skip over the barcodes that are not numbers, and only display the number ones. I have tried to use IF functions nested in Collect, and outside of Collect but I always get an error like #Incorrect Argument.

This is an example of something I've tried.

="+" + JOIN(DISTINCT(COLLECT({Barcode}, {Lot ID}, IF(ISNUMBER({Barcode}), [Enter Lot ID]1, ""))), "+") + "+"

I would really appreciate if anyone has any advice on what I should try. Thanks in advance for the help.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!