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?

3
  • Welcome. Please read how to and share a test sheet so as you can be easier helped. Jul 14 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
    Jul 14 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
    Jul 18 at 11:58

1 Answer 1

1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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