|
|
|
Zeros in Text-to-Columns in Excel
|
Original Message
|
Name: StephanieSDN
Date: January 3, 2007 at 21:59:09 Pacific
Subject: Zeros in Text-to-Columns in ExcelOS: Windows XPCPU/Ram: Intel Celeron M, 1GBModel/Manufacturer: 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!!
Report Offensive Message For Removal
|
|
Response Number 2
|
Name: Michael J (by mjdamato)
Date: January 4, 2007 at 10:19:05 Pacific
Subject: Zeros in Text-to-Columns in Excel |
Reply: (edit)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
 Report Offensive Follow Up For Removal
|
|
Response Number 3
|
Name: StephanieSDN
Date: January 4, 2007 at 16:08:16 Pacific
Subject: Zeros in Text-to-Columns in Excel |
Reply: (edit)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....
Report Offensive Follow Up For Removal
|
|
Response Number 5
|
Name: wizard-fred
Date: January 6, 2007 at 01:08:37 Pacific
Subject: Zeros in Text-to-Columns in Excel |
Reply: (edit)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.
Report Offensive Follow Up For Removal
|
|
Response Number 6
|
Name: jon_k
Date: January 8, 2007 at 07:16:54 Pacific
Subject: Zeros in Text-to-Columns in Excel |
Reply: (edit)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: =text(A2,"00000") Problem solved?
Report Offensive Follow Up For Removal
|
Use following form to reply to current message:
|
|
|
