Compare text with range

Stanislav Morozov

Hi dear Smartsheet community. Please help with a case.

I need to check if all part numbers in my cell are listed in database.

Result should list unknown part numbers. In my example it's 6a.

Format of part numbers presentation in a cell can be standardized, i.e. 1a, 1b, 1c or 1a 1b 1c (separator can be standardized).

I tried with JOIN(COLLECT), but it works if i check in reverse way - from Known part numbers to Equipment list. But i need to check it from Equipment list per each line.

Maybe I need to have multi-select drop down type of column? What formula will help in this case?

Thanks in advance!

Paul Newcome

What is the max number of parts that can be listed in a single cell?

Stanislav Morozov

Hi Paul

We can have from 1 to 50 part numbers listed in a single cell

Paul Newcome

The only way to accomplish this is to parse out each selection into its own column. We can do that via formula, but that will require an additional 50 columns. Is that feasible?

Stanislav Morozov

Thank you Paul... it's better to check

How would you parse out each section? FIND separator and then use LEN and MID? Or there is a more beautiful way?

Paul Newcome

The parsing is actually pretty straight forward. My concern with feasibility is making sure your sheet has the capacity for it. If your sheet can handle 50 extra columns with formulas, then we can go ahead and get to parsing.

Stanislav Morozov

I see Paul, thank you a lot.

Could you please share your approach for parsing. I'll try to reduce number of part numbers from 50 to lower number.

Buy the way... what if we list part numbers in children rows?

Paul Newcome

Listing part numbers as individual child rows would make this particular piece of your solution much easier, but it may impact other parts of the solution such as recording new entries. How do you add new rows, and would managing part numbers as individual child rows be feasible?