Redistributing text values in a text string in one cell into multiple cells as numbers

Database problem.xlsx.

I am a beginning-intermediate Excel user new on the job, who has inherited an Excel 2010 database that seems to be haphazardly constructed. I need to get it into shape before I can begin entering more data. The database is intended to reflect answers that clients of the agency I work for give in response to a questionnaire, which includes health information, like:

5. Do you have any of the following conditions? Check all that apply.
Cancer [check box]
AIDS [check box]
Hepatitis [check box]
Migraines [check box]
etc

The person who created the database put all checked answers for this question into one column of cells (rows are clients), in text form, strung on with commas, rather than in quantitative/numeric form, separated by column. For instance, if client Jane Doe checked cancer, hepatitis, and migraines in her questionnaire, but not AIDS, her corresponding question 5 cell (named Q5) would contain the text "Cancer, Hepatitis, Migraines". If client Sue Smith checked AIDS and Hepatitis, but nothing else, her corresponding question 5 column cell would contain the text "AIDS, Hepatitis". See in the attachment above, blue table on the left.

First, I think these data should be quantititative (numeric), not qualitative (words/text). Eventually these data may be fed into Statistical Package for Social Sciences (SPSS) or other statistical software, which uses numbers. To my understanding, as far as SPSS and like software is concerned, the word "Cancer," or any other word, is an error. Cancer may be assigned the value 1, AIDS the value 2, Hepatitis 3, and so on. Alternately, a Cancer column cell yes could be 1 and a Cancer column cell no could be 2.

Second, what are all these word/text-values doing in a text string in one cell? Some of these text strings are fifteen words long (which from a strictly visual perspective, outstrips any text-wrap manageability). Is there really any utility to representing actual data points with qualitative word/text strings? If the agency program director says to me, tell me how many of our clients have AIDS, can I extract that count from one column (Q5) of assorted text strings that may or may not contain the word "AIDS" --without doing ridiculous formulaic conniptions?

Wouldn't it be better (simpler) to separate each health condition into its own column? For example, column name Q5-1 [question 5, sub-question 1 Cancer] for Cancer, column name Q5-2 for AIDS, etc. And also give each answer a numeric value. Say yes=1 and no=2. See in the attachment, orange table on the right

So in sum my questions are:

1. Is there any legitimate reason for the data to be in qualitative rather than quantitative form, bearing in mind that it may be fed to number crunching SPSS or the like?

a. If so, is there any legitimate reason for the qualitative data to exist in long, comma separated text strings within single cells?

2. If not, is there any automated way for me to clean this up by converting the entirety of column Q5 into Q5-1, Q5-2, Q5-3, etc? In other words, a formula that would extract Jane Doe "Cancer" from the text string in Q5 and throw it into a new Q5-1 as a 1; and extract her "AIDS" from the text string in Q5 and put it in a new Q5-2 as a 2, etc? Restated, is there an automated way to take the blue table on the left of the attachment and convert it into the orange table on the right? If so, what is it?

3. Or do I have to do this manually? (In which case I may just scrap this database and build one from scratch.)


Meta