Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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



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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .