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

Please Login or Register  to view this content.

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.

It can definitely be done, and you're not asking too much.
Heck if I have time today, and no one else has got around to it, I'll take a crack at it just for practice (I still have a lot to learn about this as well!). The cells all have exactly the same number of characters, so it could even be done with formulas.

mew!

EDIT:
One question, where are the D, HB, CHB, etc. coming from on the second page?

Hi, thanks for the code, it works up to a point. For some reason it does not separate text to columns, the data is all in one cell, a message box appears after the Macro is run "Microsoft Visual Basic . X . 400". Also it does not include the other 3 cells directly below which need to be aligned to the same row and if possible the headings too, although none of the colouring is needed.

Thank you for taking the time to create this though as it still save me vast amounts of time as I do this a number of times during the week. Appreciate any/all other input in this. Thanks

I wrote one that had some kinks in it, but never finished it off because I saw someone else post here. I'll send it your way tomorrow if you still don't have an answer by then.

mew.

Eh
re-wrote one, it's pretty... crude.

Works, mostly, not sure what's happening with the format of the dates half way through though. So yeah, save a copy of the original. :P

mew

Thanks mdbct, this works and picks up all the data.

The only thing is that the date format for D:G does not seem to work on CTB records. When I try to insert a formula for cells E and F it does not seem to work for CTB records. Here is the formula :-

Please Login or Register  to view this content.

The error message in the cell is #VALUE!.

Essentially I'm trying to join your code to my own whic does not seem to be working, I can post it up if you'd like. Otherwise any help on this is appreciated.

the formatting issue was because I forgot to specify the sheet whose columns were to be formatted.

The CTB issue is due to there being a space in front of the time, so Excel is interpreting it as a text value instead of a Date Time value.

Try this version.

Please Login or Register  to view this content.

Addendum: I forgot to mention that I added your formula to the L column