# Solved: Excel - Text to Row?



## DrewMcK (Jan 12, 2007)

I am not sure exactly the topic I need help with, but I geuss it is some sort of parsing.

I have data in colums. Day, Date, Time, location, and of course I was not thinking when I was setting the spreadsheet up.

The time column has multiple times in one cell. I need to take these times and split them into individual cells in rows on top of each other, and for each time put that date, day, and location next to it. For location, is there is only one location that means it applies to all of the times, for the others the location line corresponds to that lines time. 

Right now there is only one day and date for many times, but once these times are split up there will be repeating days and dates and location for each time.

I have attached the spreadhseet, as I havn't done an excellent job of explaining. But I assume once you see it you will know what I mean.

Some are already in this format because there was only 1 time on that day.... and there are lots of blanks.....

Any help is appreciated, thanks again guys.


----------



## DrewMcK (Jan 12, 2007)

well, I got it.... I had to use a longer way than a macro, but using a substitute, I substituted all of the line breaks into commas, then delimeted them, then added enoguh rows for each date, then special pasted and transposed. It saved me time comapred to redoing it all, although I don't know how much.....


----------



## Zack Barresse (Jul 25, 2004)

So you don't need a solution right away? I was working on a macro right before lunch (just before you posted). If you don't need it, cool beans, just let us know.


----------



## DrewMcK (Jan 12, 2007)

Yea, I fixed it using a few steps. Thanks though firefytr, you really know your stuff.

Related to this spreadhseet however, I will be doing some pretty in depth formulas for which a macro may be better. But I will PM you when I get to that point if you don't mind.

On another point, the way I had it setup with multiple lines in each cell, how hard is it to reference specific lines in those individual cells?


----------



## Zack Barresse (Jul 25, 2004)

Well, your data is best setup where each record is on a single line, much like you would in Access. You shouldn't be doing any Alt + Enter in any cell. That information should be put on a new row/record. It's always easier when you break it down like that. Treat it like a database, the only difference being the computing power which comes with Excel. If you aren't going to use Excel features and functionality for it, you may want to think about using Access or another relational database.


----------

