Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have some data in which one column represents the Days of Operations, as below:

-------
Days
-------
15
7
1234567

etc.

I want to "break" the column (via text to columns option in the Data tab of Excel) and create the following columns:

--------------------
D1|D2|D3|D4|D5|D6|D7
--------------------
 1|  |  |  | 5|  |  |
  |  |  |  |  |  | 7|
 1| 2| 3| 4| 5| 6| 7|           

However, the data is being messed up and I get different days in different columns... How I can overpass this? Is there a way to assign the values accordingly?

share|improve this question
    
It is not clear how your source data is structured. What are the parameters for the Text-to-column operation? Your source looks like the first row is the number 15. The second data sample shows the 1 in the first column and a 5 in the 5th column. Is that the desired result? Please post links to the data source and to a mocked up sample of the desired results. How would Excel know how to split the first row across the columns involved? What is the logic? What are the rules? Is the data in a format that can be processed according to these rules? Please provide more background. –  teylyn Apr 15 '13 at 10:00
    
Yes, the desired result is what you understood. Initially, there is only one column named 'Days' which contains numbers as in the first display. D1 stands for Monday, D2 for Tuesday, D3 for Wednesday, etc. I want to create a separate column for each day. For example, if in the first set (Days) there is '15', I would like 1 to be stored in the new D1 column, D2-D4 to be blank, D5 to have 5 and D6 and D7 to be blank as well. –  Dimitris Apr 15 '13 at 10:22
add comment

1 Answer

up vote 3 down vote accepted

I want to "break" the column (via text to columns option in the Data tab of Excel)

As per your question, you want to do this with Text-to-Columns. Well, that's a no go. Text-to-columns works either on a character delimiter or a fixed width. Your data has neither. You apparently want to distribute the data based on its values. That cannot be done with Text-to-Columns.

You will need a code or a formula solution for that. If you import your source data into a spreadsheet, with all the imported data sitting in column A, you can use a formula like this:

=IF(ISERROR(FIND(COLUMN(A1),$A4)),"",COLUMN(A1))

If the data as posted above starts in A1, then the formula will be in cell B4 and can be copied across and down.

enter image description here

You can hide column A do display just the results of the formula.

share|improve this answer
    
Fantastic! Thanks a lot for your answer :) Truly appreciate your help! –  Dimitris Apr 15 '13 at 11:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.