Need macro to split one column into multiple columns based on _ and spacing

1010_BIERMAN_5530_07092012.pdf 8/31/2012 6:00:22 PM

10469_EVANS_5530_07112012.pdf 8/31/2012 6:00:22 PM

1048_CASTIGLIONE_5510_08212012.pdf 8/31/2012 6:00:22 PM

10499_LAUDERDALE_5520_07052012.pdf 8/31/2012 6:00:22 PM

Okay folks. I Need each of the examples split up (in a macro) to seperate everything before/after a _ to a seperate column.
There are three exceptions
1)***HOWEVER, the .pdf can get taken out of the end***
2)***I need to convert the last numbers (before the .pdf) into date format (MM/DD/YYYY) (I.e 07052012 would be 7/5/2012)
3)***If the cell contains something like 8/31/2012 6:00:22 PM (after a bunch of the spaces) then please insert This Date(+TIME) into ANOTHER column. I am not certain each pdf name will have a separate date attached, but for this example, they are***

In Total, 5 columns (correct me if I am wrong)

This would be a huge life saver. Thanks so much guys!

Hi, it seems you need text to columns for separation. (use "_" seperator as you stressed above). Data/data tools/text to columns

Originally Posted by mike7952

It would be better if you upload a real data example with all possible scenario in one sheet and on another sheet show the output.

test.xlsx

Please reference sheet 'From JT' which would show examples of what I am looking for.

Any help with automating this as fast as possible would be great

Thanks

Formula:

Please Login or Register  to view this content.

Originally Posted by mike7952

This will work on the from jt worksheet.

Please Login or Register  to view this content.

will need to adjust 3 to your starting row.

Please Login or Register  to view this content.

It is not working. It is returning 07092012 when I need that to auto-convert that column to 7/09/2012. I cannot do it manually. Then there is a column to the right of that that is returning only 0. That I am not so worried about.

Perfect Mike. However, Column I may not always be there. There are situations where we need two dates. The number in column H should be converted to dd/mm/yyyy, that is more along the lines of what I was looking for.

IFF there is a 69725_MORTIMER_5530_07132012.pdf 9/4/2012 4:00:23 PM then I would need column I

Otherwise

I would need exactly what you gave me but instead of '7132012' in column H I would want 7/13/2012 done automatically for me.

Can you make it work universally? Regardless of the amount of characters?

Thank you for including this different method - It has exactly what I need. I appreciate everybody's help in resolving this matter - now task #2 will be posted shortly!

Need the best of the best for this one

Regards

Dan