Compare/Sepearte Values from Cell

R

Ren

I have a set of survey data in Excel format. Some questions are in the format
of "check all that apply" and the answers i have are in a single column of
cells with each answer seperated by a coma. The answer could also be "other".
For some reason, the survey doesn't mark the answer as other, but simply
record the data, so i can have answers like

A,B,C
C,E,custom input data1
A,custom input data 1

I want to get the data into seperate columns with binary indicating values
like
A | B | C | D | E | Other | Value of Other
1 1 1
1 1 1 custom input data1

How should I make this happen
1) Is there any excel function that interprets a filed with a contain like
function. The only one i know of is search(), but search() returns error
"#Value" when it fails to find a match and I don't know how to convert
"#Value" to a useable value understood by other functions like if()

2) How should I get excel to understand the "other" field and parse the
answer from the preceding cells. Is there anyway i can do this without a
macro. If i need to write a macro, how do I make a macro parse the data as I
showed above.
 
R

Ren

Nevermind. I figured this out using a combination of search(), IsError(),
Len(), and Right() Functions. A macro wasn't needed.
 
Top