Query complete sheet and copy to another sheet

I am wanting to clean up data from a excel sheet and copy only needed data to a new sheet in the same workbook. The problem is all the data is stored in column A and is listed in the following format.

Name: LDAP Search
Source: MSAccess
Description: LDAP Search time 6.7 sec
Time: 4/8/2007

The above data is all stored in column A and need to be copied onto a new sheet in seperate colums for each item as listed below.

Column A Column B Column C Column D
Name Source Description Time
LDAP Search MSAccess LDAP Search time 4/8/2007


Is this something can be done or do i need to do this all manually?

Thanks for any suggestions.

Hi

Could be done either with formulas or a macro.

Can you give more details on where this data is column A starting in row 1 etc. Especially if there are any line breaks etc between each record.


rylo

Here is an example of the data in the spreadsheet: This data is all listed in Column A of sheet 1 in the excel workbook.

Domain: ABGM
Computer: ABGMSRV01
Time Last Modified: 5/8/2007 4:46:02 PM
Resolution State: New
Time in State: 4/22/2007 8:26:01 PM
Problem State: 0
Repeat Count: 93
Name: DSAccess:LDAP Search Time > 50 ms avg. over 5 minutes
Source: MSExchangeDSAccess Processes: Ldap Search Time: 3900
------------------------Line break-------------------------------
Domain: ABGM
Computer: ABGMSRV01
Time Last Modified: 5/6/2007 4:46:02 PM
Resolution State: New
Time in State: 3/2/2007 8:26:01 PM
Problem State: 0
Repeat Count: 684
Name: DSAccess:LDAP Search Time > 50 ms avg. over 5 minutes
Source: MSExchangeDSAccess Processes: Ldap Search Time: 3900

Hi

Assuming that your data as shown is in the range Sheet1!A1:A19 then in sheet2!A1 enter the formula
=OFFSET(Sheet1!$A$1,(ROW()-1)*10+(COLUMN()-1),0)
Copy across to I1 then down as required.

This should split the data across the columns.


rylo