Cleaning Messy Data for Import

The following code, is adapted from some help I received from davegugg. I asks the user to open a file from a directory, and then it searches for unique identifiers in the newly opened book. There is no problem with the search and I am pleased at the speed for which it finds all 37 of them in a workbook that has 145 sheets. Anyways, here is the code:

Please Login or Register  to view this content.

The problem lies with what to do next, if you open the workbook, you'll see that the data is not exactly displayed in a great way. Also, there is a lot of dirty data that needs cleaning. For instance, the months from the source are not of the Date type because some of them have dots at the end of them (Jan. | Feb. | Mar | Apr | May. | & etc...). Anyhow, can someone suggest to me how I can clean the data before I dump it into my staging workbook? I can figure out the arrays part and transfer parts but the cleaning of the data often confuses me.

Kindest Regards:

You forgot to add the sheet what the result should look like.

I did some other cleaning:

Please Login or Register  to view this content.