Separating text into new columns

[MikeS]

Hi, I am having a problem splitting my delimited text file into ne
columns. What I have to do is take several survey results (all in
long string of text) and give each survey its own column so I ca
analyize it. So what I need to do is, at the end of the first survey
have some sort of a column break or something to that effect so th
next set of data places itself in a new column. Here is a smal
sample of what the data looks like:

9.)|Infrastructure|ACCEPTED|Type your comments here.|
10.)|Infrastructure|ACCEPTED|Type your comments here.|

Date: Wed, 03 Dec 2003 12:16:32 -0500 (EST)

In this example I would need to have the "Date...." start in a ne
column.
Does anyone have any idea how I could do this? Thanks in advance.
~Mik

 

[Frank Kabel]

Hi
you may try 'Data - Text to columns' and choose your delimiter
accordingly

 

[MikeS]

Thanks for the help but the problem is greater than that. If I di
that, it would just put the "date..." into the next column where
would actually need the following 250 rows to go into that column wit
it. Then I would need the next survey to do the following but in
third column, etc. I was wondering if maybe there is a way to grou
each of the surveys and then tell each one to go into its own column?
Thanks again

 

[Dave Peterson]

All this is in column A?

If yes, then how about a little manual work?

Insert a new column A.
=if(right(b1,5)="date:",b1,"Detail")
and drag down

Convert column A to values (copy|paste special|values).

Filter on column A.
Custom filter
does not equal Detail
(now only the date rows are in showing in column B)

Select that range and clearcontents.

Data|filter|showall data

Do your data|text to columns against column B.

If you decide you want the date on each row in column A, you can use the
technique describe at Debra Dalgleish's site to fill those empty cells.

http://www.contextures.com/xlDataEntry02.html