Distributing a list of numbers in one column to several columns

[Jwhite25]

Dear all,

I'm working with a dataset from a survey that has recently taken place. The original structure of certain questions in the survey were multiple choice. The ideal structure for these questions, in terms of variables, is that each possible option for the question (A,B,C,D... etc) have a separate value, with a 1 for yes (reported) and 0 for no (not reported). In other words, if Question1 has multiple possible answers (A,B,C...), then there should not be one variable created from this question, but rather, there should be a variable for each possible response: Question1_A, Question1_B, Question1_C, and so on. And within each of these variables, should be a 1 or 0 depending on if that option was reported or not.


The format I received the data in, though, was a single variable with a list of letters, (e.g. B,D,F) for each option reported.

Variable: Question1
Row 1 A, B, C
Row 2 B, E, F
Row 3 B, D, E

The data I have right now is housed in Excel prior to exporting to SPSS and/or STATA. I need to find a way to distribute these values into separate columns (variables). The tricky part, is that if I simply use "text to columns", it distributes the letters to new columns, but doesn't take into account that the first value reported might not be A. Is there a way to take the list of values in the single columns, and distribute them to assigned newly created columns?

Any help with this would be GREATLY appreciated!

Thank you in advance for any advice.

Best,


Jessie

[aydeegee]

Hi Jessie

Welcome to the forum.

Just so that here is no misunderstanding, are you able to upload a sample of what you have?

Go to: Reply / Go Advanced / Manage attachments

Regards
Alastair

[aydeegee]

Double post deleted

[Jwhite25]

Yes, no problem. I've attached an excel file here with an example of one variable. What I need to figure out, is how to take the values in CBN15, and distribute them to the appropriate columns (CBN15_A, CBN15_B, etc), and then convert that to 1 (reported) or 0 (non-reported), without also entering in 0 values for "missing" (the rows without any values).

[MarvinP]

Hi Jwhite and welcome to the forum,

Excel has a feature called "Text to Columns" which is an Icon located on the DATA Tab.

Simply select your entire column A data and then click on Text to Column and pick the comma as the delimiter. This will do what I think you want.

See http://excelhints.com/2010/01/29/usi...separate-data/ for some examples.

[Jwhite25]

Hi MarvinP,

Thanks for your response. I did try Text to Columns, but it doesn't take into account that each row doesn't start with A. Therefore when I use Text to Columns, in my first new variable (CBN15_A), I may get a B or C or D, depending on the first letter mentioned in the original column. Is there a way to account for this?

[MarvinP]

OK - I think I've got your answer..

See the attached and look at the formulas. I changed the Row 1 names to make my formula easier.