G
Guest
Hello,
As part of a project, I was conducting a survey (administered online) using
Snap survey software. Snap automatically sends results into an Excel file,
which is great. However, several of the questions allowed participants to
select more than one item ("Check the top 3 things..." etc.). These responses
were coded in Excel into a 10101 format, where 1=checked and 0=not checked,
all in one cell. Since 0 represents a response, I selected the cell
formatting of these columns as Custom (Format->Cell->Number tab->Custom), so
any 0s before the first 1 would not drop off.
But the problem happens when I go to split these
multiple-responses-in-one-cell into their own individual columns. For
example, let's say that Column C represents a question where people could
check up to 3 items, and one respondent did not check the first two and
checked the last one. The cell, then, would appear "001." I need to split
that column into 3 columns, one which will represent the first item, one
which will represent the second item, etc. However, when I use the
text-to-columns function, it does not recognize any 0s in front of the first
1, even though they show up that way on my Excel sheet. Using my above
example, the result I keep getting is the first new column contains a 1, and
the next 2 columns are blank -- it just kicks out the 0s automatically if
they are in front of the first 1.
Is there any other way I can format my cells to lock in those zeros so that
text-to-format recognizes the 0s? Or is there another function I can use to
split those cells into individual columns? Keep in mind that there are no
formulas in my current dataset, just numbers.
Any help would be most sincerely appreciated!!
As part of a project, I was conducting a survey (administered online) using
Snap survey software. Snap automatically sends results into an Excel file,
which is great. However, several of the questions allowed participants to
select more than one item ("Check the top 3 things..." etc.). These responses
were coded in Excel into a 10101 format, where 1=checked and 0=not checked,
all in one cell. Since 0 represents a response, I selected the cell
formatting of these columns as Custom (Format->Cell->Number tab->Custom), so
any 0s before the first 1 would not drop off.
But the problem happens when I go to split these
multiple-responses-in-one-cell into their own individual columns. For
example, let's say that Column C represents a question where people could
check up to 3 items, and one respondent did not check the first two and
checked the last one. The cell, then, would appear "001." I need to split
that column into 3 columns, one which will represent the first item, one
which will represent the second item, etc. However, when I use the
text-to-columns function, it does not recognize any 0s in front of the first
1, even though they show up that way on my Excel sheet. Using my above
example, the result I keep getting is the first new column contains a 1, and
the next 2 columns are blank -- it just kicks out the 0s automatically if
they are in front of the first 1.
Is there any other way I can format my cells to lock in those zeros so that
text-to-format recognizes the 0s? Or is there another function I can use to
split those cells into individual columns? Keep in mind that there are no
formulas in my current dataset, just numbers.
Any help would be most sincerely appreciated!!