Converting a Single Column of Information to a Database

[candeos]

Hello

I have a bunch of information that is currently in a single column and 3,441 rows of an excel spreadsheet It looks like this"

Record 1 Name
Record 1 Address
Record 1 City
Record 1 State
Record 1 Zip
{space}
Record 2 Name
Record 2 Address
...
...

The "records' are always separated by a space and most of the time have the same number of elements Some may be missing the last element before the space

How do I convert this to look like this across multiple columns and rows?

Record 1 Name Record 1 Address Record 1 City ....
Record 2 Name Record 2 Address Record 2 City...

I tried using a macro, but I dont know how to make it search for a space and write the data to the next row in the final table.

Thanks in advance for the help!

Dave

 

[ETID]

'Assuming that the data is in col A in sheet1 and sheet2 is blank!
'paste this into a module and run it

Sub normalize_data()
Dim r As Integer
Dim new_r As Integer

Sheets("sheet1").Select

r = 1
new_r = 1

Do Until r > Sheets("sheet1").UsedRange.Rows.Count
For i = 1 To 5
Sheets("sheet2").Cells(new_r, i) = Cells(r, 1)
r = r + 1
Next i
new_r = new_r + 1
r = r + 1
Loop

End Sub

 

[ETID]

....I assumed Excel, but I can post an access solution as well.

 

[candeos]

Thanks for the assistance...I'll try it tonight!

Dave

 

[ETID]

Did this work for you?