I'm afraid this can't be done with a simple formula. The data is too unstructured to say so. In the past I sometimes got these kind of data and tried fixing it to a normal Excel table format, but with lot of work. Semi handwork, semi formula work or just copy-paste.

Maybe it's possible with programming a macro, but that's beyond by capacities. Hopefully somebody else here can help you.

Hi Dritan,

This is the perfect job for Power Query. See example attached (refer to the second file; dritan_query-1.xlsx). On the 'Query' sheet is your desired output which is linked to 'The Table' sheet. Note: I've changed the file type to .xslx. .xls is an old file type for use with Excel 2003. I hope you're not still using that 🙂

If you'd like to learn Power Query you can do so here.

Mynda

Hi Dritan

You can give this macro a try. I have changed the file type to xlsm.

It will extract the data to the Output worksheet.

Hope this helps.

Sunny

Thank you Frans.

Thank you for great solution Sunny.

In Excel I'm much more familiar with formula, and I face some difficulty using macro.

Thank you Mynda,

I tried to download your file but the file it doesn't found :(.

Can you send me again?

thx,

Dritan

Hi Dritan,

I've uploaded it again here. Hopefully that one works.

Also, in future please don't insert the prior posts as quotes. Just reference the person who helped you in your reply. It just makes the post easier to read.

Thanks,

Mynda