Text to Column Function Rejects Zeros
 

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!!

Text to Column Function Rejects Zeros
 

I should specify that I am using Excel 97 - sorry.

"Stephanie" wrote:

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!!

Text to Column Function Rejects Zeros
 

You *must* format your cells to Text, *before* you enter any of your data.
Then, you will have to key in *all* your values, including the leading and
trailing 0's.

Then TTC will work for you.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Stephanie" wrote in message
...
I should specify that I am using Excel 97 - sorry.

"Stephanie" wrote:

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!!

Text to Column Function Rejects Zeros
 

Put this formula in the column next to your data, then copy down select the
whole column and copy paste special values and it will leave zeroes in and
in text format. TTC away!
=TEXT(A1,"0000")
assuming data in A1 and 4 digits, add or subtract 0's for more/less
--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"RagDyer" wrote in message
...
You *must* format your cells to Text, *before* you enter any of your data.
Then, you will have to key in *all* your values, including the leading and
trailing 0's.

Then TTC will work for you.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Stephanie" wrote in message
...
I should specify that I am using Excel 97 - sorry.

"Stephanie" wrote:

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!!

Text to Column Function Rejects Zeros
 

Oh, this worked PERFECTLY! Thank you SO MUCH!!!!!!!!


"John Bundy" wrote:

Put this formula in the column next to your data, then copy down select the
whole column and copy paste special values and it will leave zeroes in and
in text format. TTC away!
=TEXT(A1,"0000")
assuming data in A1 and 4 digits, add or subtract 0's for more/less
--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"RagDyer" wrote in message
...
You *must* format your cells to Text, *before* you enter any of your data.
Then, you will have to key in *all* your values, including the leading and
trailing 0's.

Then TTC will work for you.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Stephanie" wrote in message
...
I should specify that I am using Excel 97 - sorry.

"Stephanie" wrote:

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!!