Formula to print 'X' if comma separated string value = '<value>'

Question

I am trying to have Excel print 'X" in a table column based on if a specific value is found in a comma separated string given to me from a Microsoft Form.

I removed the characters I don't want, and now my comma separated string is just that, comma separated only.

I have tried a formula where if I search the string and it finds the value I am looking for, it returns to the corresponding table column with 'X'.

Original Output from Microsoft Forms:

["ViewPoint","B2W Estimate","Nektar Equipdata"]

I have used this formula to strip characters that I don't want/need.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Applications[Applications], "[", ""),"""",""),",",", "),"]","")

This is the formula I am trying to use to get the corresponding column to input what I want.

=IF([Column7]=(SEARCH("ViewPoint",[Column7])),"X","")

Here is the expected and the actual result :

Expected Result: "X"
Actual Result: "#NAME?"

Answer 1

I believe you get he #NAME? result if the searched item doesn't contain the string of interest, because it is checking "Does myString = #ERR ?"

In addition to the other answer, you could also try: IF(ISERROR(SEARCH(...)),"X","-")

Answer 2

This should work, if i understand your question correctly.

=IF(ISNUMBER(SEARCH($B$1,A4)),"X","-")

If you want the total number of occurances in the column, you need this ARRAY Formuly CTRL + SHIFT + ENTER

=SUM(ISNUMBER(SEARCH($B$1;A4:A7))*1)