Transposing Unequal, Repeating Rows to Columns

Hello,

I found the answer to this question about 6 months ago, but now it is eluding me.

I'm trying to transpose ugly old data from an AS400 system into Excel. I don't want to put the actual information on a public forum, but a close approximation would look like this:

Student 1, Street 1
Student 1, City 1
Student 1, Phone 1
Student 1,
Student 2, Street 2
Student 2, Apartment 2
Student 2, City 2
Student 2, Phone 2
Student 3, Street 3
Student 3, City 3
Student 3, Phone 3
Student 4, Street 4
Student 4, City 4
Student 4, Phone 4
Student 4,
Student 5, Street 5
Student 5, City 5
Student 5,
Student 5, Phone 5
Student 5,
Student 5,
Student 5,

As you can see, the data is a mess. Usually, the data is grouped into 4 rows, but sometimes it's 3 rows and other times more. Sometimes the data skips a line between the city and the phone number. Sometimes, there is no city listed, just a street name and number. Sometimes there are 3 or more blank lines after a listing.

Column one is a student number, and column two is the various data types.

Because the data is in such disarray, I don't see how any solution will be perfect. But, if I can at least break out the rows into columns, I think that I can figure it out from there. a good solution would look like this:

Student 1, Street 1, City 1, Phone 1, *Blank
Student 2, Street 2, Apartment 2, City 2, Phone 2
Student 3, Street 3, City 3, Phone 3
Student 4, Street 4, City 4, Phone 4, *Blank
Student 5, Street 5, City 5, *Blank, Phone 5, *Blank, *Blank, *Blank

From there, I can at least get started. Any help is much appreciated. Thanks!

Hi and welcome to the forum.

Is your data in a single column or two columns?

2 columns, I updated the original post to make this explicit.

And thank you for the welcome.