Ron,

Put your list into column A of one sheet. On another sheet, starting in Cell A1 and going across
Row 1, put the identifiers for the data that you want to extract/remove from your datatable:

ID # :
Sex :
Raw Scores :
T Scores :

These whould be entered into A1, B1, C1, D1. Note the inclusion of the colon.

Then select those cells (you can have as many as you want, as long as the strings appear within your
database) and name them "Headers" (Select them, then use Insert / Names... Define...)

Then select your sheet with the database of values, and run the macro below.

If you cannot get it to work, I will send you a working example.

HTH,
Bernie
MS Excel MVP

Sub MakeDataBase()
Dim myData As Range
Dim myArea As Range
Dim myCell As Range
Dim i As Integer
Dim myRow As Long

myRow = 2

Set myData = ActiveSheet.Range("A:A"). _
SpecialCells(xlCellTypeConstants, 2)

For Each myArea In myData.Areas
For Each myCell In myArea
For i = 1 To Range("Headers").Cells.Count
If InStr(1, myCell.Value, Range("Headers"). _
Cells(1, i).Value) 0 Then
Range("Headers").Parent.Cells(myRow, i).Value = _
Application.Trim(Replace(myCell.Value, _
Range("Headers").Cells(1, i).Value, ""))
End If
Next i
Next myCell
myRow = myRow + 1
Next myArea

With Range("Headers").Parent
..Range("D:I").EntireColumn.Insert
..Range("C2", .Cells(Rows.Count, 3).End(xlUp)).TextToColumns _
Destination:=.Range("C2"), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True
.Range("C1:I1").Formula = "=""Raw Score "" & Column()-2"
.Range("C1:I1").Value = .Range("C1:I1").Value

.Range("J2", .Cells(Rows.Count, 10).End(xlUp)).TextToColumns _
Destination:=.Range("J2"), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True
.Range("J1:P1").Formula = "=""T Score "" & Column()-9"
.Range("J1:P1").Value = .Range("J1:P1").Value
End With

End Sub



wrote in message s.com...
Bernie:

Thanks for the help. It might just be easier if I can send you the
file, since it is quite small. The format is consistent as follows:

ID # : 10106
Sex : Female
Raw Scores : 1 4 1 0 3 6 3
T Scores : 53 114 65 43 64 83 42

ID # : 10102
Sex : Female
Raw Scores : 0 0 0 10 4 0 14
T Scores : 41 43 44 56 64 41 52

ID # : 10104
Sex : Female
Raw Scores : 0 0 0 11 8 0 19
T Scores : 41 43 44 58 89 41 60

ID # : 11082
Sex : Female
Raw Scores : 0 0 0 8 3 0 11
T Scores : 44 47 46 66 64 42 63

ID # : 11084
Sex : Female
Raw Scores : 0 0 0 3 1 0 4
T Scores : 44 47 46 52 58 42 45

ID # : 11086
Sex : Female
Raw Scores : 0 0 1 3 4 1 7
T Scores : 44 47 65 52 66 49 54

ID # : 11262
Sex : Female
Raw Scores : 6 4 7 0 6 17 6
T Scores : 75 84 106 35 77 96 40

We need to pick up the ID#, Sex, each of the seven Raw Scores and each
of the seven T Scores. There are always seven of each, but as you can
see, they can vary from a single digit up to theree digits. I don't
have an exact count on the number of data sets in this file, but the
sets will vary depending on the file we are exporting. There are
always four rows, with one row separating the sets. I don't know if
the single space separating the scores is a true space or an ASCII
character. There seems to be a couple of spaces leading up to each
row, before ID#, Sex, Raw Scores and T Scores also.

I hope this is the info you need, and please let me know if you would
prefer to see the entire file.

I appreciate the time you have taken to help me!!

Ron

Bernie Deitrick wrote:
What column is the data in?
Can the number of sets vary or is it always 700?
What is the first row of actual data? Does that vary?
Are there always four rows of data from each set? Or is it based on the
leading label?
Is there a header row?
Is/are there a/many blank row(s) separating the data sets? If so, how many?
Are there always 7 numbers in each of the scores?
How many spaces between numbers? Are they really spaces or another ASCII
character?

Post back with the answers, and I will whip you up a quicky macro that will
put your data in order... or maybe request a file that you could send to
me....

HTH,
Bernie
MS Excel MVP

wrote in message
ups.com...
I have a data set of about 700 entries from an old Pascal program that
was exported to a text file in this fomat:

ID # : 10106
Sex : Female
Raw Scores : 1 4 1 0 3 6 3
T Scores : 53 114 65 43 64 83 42


I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and
then to pick up the data and place it in the appropriate cell. Since
the format is inverted, Excel cannot import is correctly. Is there a
script that will accomplish this?

Thank you for your help!!