Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.
Zeros in Text-to-Columns in Excel
Name: StephanieSDN Date: January 3, 2007 at 21:59:09 Pacific OS: Windows XP CPU/Ram: Intel Celeron M, 1GB Product: Dell Inspiron 1300
Comment:
I recently did a survey for a grad school project, and my results were rolled up in Excel. For questions where more than 1 answer could be given, the cell value is in a "01010" pattern, where 1=checked, and 0=not checked. I formatted those columns using the "Custom" type in Cell Formatting. Then, when do a text-to-columns split, the zeros in front of 1s do not register! Does anyone know how to preserve the zeros in a text-to-column? The data set is simply too large to make individual cell adjustments. PLEASE HELP!!
Name: wizard-fred Date: January 4, 2007 at 03:35:51 Pacific
Reply:
Split The "01010" into individual text columns.
0
Response Number 2
Name: Michael J (by mjdamato) Date: January 4, 2007 at 10:19:05 Pacific
Reply:
Well, that's what her problem is about. I'm assuming you have a formula to create that number, just put the formula for each answer in different columns.
Or, change your formula to use "YNYNYN" instead. Many ways to attack this issue.
Michael J
0
Response Number 3
Name: StephanieSDN Date: January 4, 2007 at 16:08:16 Pacific
Reply:
Here is the origin of my datafile: the survey was administered online, using Snap Survey Software. Snap automatically generates the results into an Excel file. Therefore, I have no formulas, just the numbers.
I appreciate the responses so far....
0
Response Number 4
Name: Nigel Spike Date: January 5, 2007 at 07:12:18 Pacific
Reply:
Could this help ? =IF(cell<10000;"0";"1")
Nigel
I support Bigger feet for rabbits
0
Response Number 5
Name: wizard-fred Date: January 6, 2007 at 01:08:37 Pacific
Reply:
If the data is returned as a text string of fixed width, like the example '01010', add columns in the spreadsheet and use a formula like '=value(mid(cell, position, 1))' to separate the value for each position. You then could use the result to 'weight' the answer.
NOTE: An apparent paradox, one's not in the first position evaluate to -1 instead of the expected 1. I use Excel 2000 under Win98SE.
If you have such a large data set probably the results should be exported to some database and the results analyzed by a statistical application. The data field in question could be parse by either the database or the analyzing package.
0
Response Number 6
Name: jon_k Date: January 8, 2007 at 07:16:54 Pacific
Reply:
Could you kick the file out to a .csv instead?
When you import a csv file in Excel, it will give you an option to format that row as text, and as a result leading 0s will be preserved. Alternatively, use:
Summary: If I read this correctly, what you are wanting to do is create a field called Names and another called Ages, which you can then use in a MailMerge, inserting the fields as you wish within documents? T...
Summary: In Excel 2007 go to Data, Text to Columns Select Delimited, Next, choose Space and finish. It is similar in other versions so look for "Text to columns" or use Help to locate it. Bryan ...
Summary: I'm not sure why your paste function would have pasted rows of data into single column. Never heard of that before. Are you sure that each row of data isn't really pasted into a single cell in Column ...
0 
