Separating text in one cell into individual cells

Hello, I have a spreadsheet which needs formatting I was wondering if anyone would be able to help with creating a macro to do so. The problem with raw report is that in cell A47 there are five columns worth of data in that one cell, then in B48 there are another two, in b49 and b50 one respectively. I manually format it by first joining the separate cells using

=A47&” “&B48&” “&b49&” “&b50

Then convert text to columns. There are 4 tables of data in the Raw report sheet, D – HB / CTB and C – HB / CTB which I join to create into 1 table, but I index each table D – HB and D – CTB ect so that I can use this to filter the table. The raw report always comes out in this format but can vary in length, i.e the amount of records it produces, I was hoping to save some time formatting this as I need to do this on a regular basis.


I’ve attached the file to show how the raw report is received and how it needs to end up. I have no knowledge of how to write VBA.

I hope this makes sense, thanks in advance.

If this is not possible or its not the done thing to request a macro from scratch please let me know and I will delete.