Transforming a sheet of unique strings to a new sheet with the values

Basically he has a system that generates a report as one excel file containing only one column and several rows for each register. He wants to use the data of that report for new reports and here is my problem, how to take every value and organize it in a clean-view new table? Here is how the sheet looks in a raw way:

I worked my way to take only the desired part of every stri开发者_StackOverflow中文版ng but now I'm stuck with a non-formatted sheet as follows:

The desired result to be shown is as follows:

The client will copy/paste the raw report from his software to the sheet1(RAW DATA) and I need to work on it to show it as desired on sheet3(CLEAN DATA), sheet2(SEPARATED DATA) I use for substring the rows and select only what I need (as the second image shows).

If you don't want to explain the entire process, just give me the excel function names that I need for this and I'll work my way to it...

---

You could create the Sheet3 from Sheet2 with vba (deleting empty rows...).

Anyway, if you still want to use formulas, you will have to use array formulas as decribed in this thread: https://stackoverflow.com/questions/1169187/find-first-non-empty-cell-in-a-spreadsheet-row.

Feel free to update your question with the formula or the vba you built so that we could help you more.