Formula to take a column to row, delimited by certain text string

Sorry for the confusing title. I Have a column of text that looks like this:

Riley Construction
123 main st.
everytown, ca
[email protected]
734 923 9482
industry type: 3

West Town Towing
19587 main st
othertown, wa
industry type: 6

coast to coast
8393 main st
unit 93902
everytown, ca
industry type: 9

My problem is some of the companies entries consist of 3 rows or up to 5 but the last row always starts with "industry type:". I was hoping to take each company entry and turn it into a row. Is that possible? I use to use this formula to do something similar but that was when each company always would be 6 rows: =OFFSET($A$1,0,(COLUMN(A1)-1)+((ROW(A1)-1)*6))

Is there anyway to have it start a new row after each of the industry type? Thanks and sorry for the confusion.

Hi Ace_XL thanks for the formula but unfortunately it doesn't seem to do the job. I might not be following it right but it seems to take very X row and start a new row but some of the entries have 4 rows and others have up to 8 but the last cell of every entry starts with the text string "Organization Type:"

Thanks for the help but unfortunately the original formula doesn't seem to work. I have downloaded the sheet you attached and copied over the formula but on the 5 company entry something goes wrong and it throws off all the rest. Not sure why because the 5th entry and all of the rest of them end with the "Organization Type:" cell.

Originally Posted by Special-K

Here's a quick VBA solution

Please Login or Register  to view this content.

It runs down the list to the end and puts each row into a new column, stepping to a new line on each "industry type: "
Run it then just do a replace "industry type: " with null (sorry I couldnt be blowed to program for that)

Hi Special-K, I figured out what I was doing wrong the other week with your VBA but it just seems to transpose the row to a line. Like after the VBA is ran all the data which use to be in something like 3,000 cells all in one column are now all in one row. Any help in what I might be missing?