0

I have a column which contains a comma-separated list of numbers: "3, 5, 11"

I need like to have a continuous bunch of cells, each with the value true or false based on whether the corresponding cell value exists in the CSV string. IE cell 1 would check for "1" in the string, cell 2 for "2" in the string etc.

SEARCH() isn't a good solution, because searching for "1" would yield true even if only the value 11 exists (and not 1).

So in a sense, it's about doing a SPLIT, then searching the result of that function.

What would be the formula for that in one of those cells?

Improve this question
3
  • Welcome. Please read how to and share a test sheet so as you can be easier helped.
    – marikamitsos
    Commented Jul 14, 2022 at 11:47
  • 1
    Welcome! Yes, SPLIT() combined with MATCH() will do the trick: =IF(ISERROR(MATCH(<tested value - your 1 or 2 or 11>,SPLIT(<cell for testing>, ", " , 1,1),0)),FALSE(),TRUE())
    – JohnSUN
    Commented Jul 14, 2022 at 12:55
  • Thanks @JohnSUN ! That solved my issue. If you were to post it as an answer I'd mark it as solved.
    – OpherV
    Commented Jul 18, 2022 at 11:58

1 Answer 1

Reset to default
1

SPLIT() combined with MATCH() will do the trick:

=IF(ISERROR(MATCH(<tested value>,SPLIT(<cell for testing>, ", " , 1,1),0)),FALSE(),TRUE())
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.