Separate a string

[mrexcelrc]

Hello,

I need help separating a string in cell AI. The string is:

X0000357_1212011_USD_1359376.33 which is concatenated DealID "X0000357, Date "12/1/2011" Currency "USD" and Amount "1359376.33. As you can see they are separated by underscore _. In B1 I need a formula to extract the Deal ID X0000357; in C1 I need a formula to extract the Date 1212011 and format it 01-dec-2011; in D1 I need a formula to extract currency USD and E1 a formula to extract the amount 1359376.33.

Please help. Thank you.

 

[NateO]

You're quite right, Rick. I didn't think through my response closely enough, while we both seem to agree there might be a problem...

The only way I see a solution to this problem is that the input has a forced mask, if you will, along the lines of mm/dd/yyyy. And use leading 0's where applicable - then it's easy to cut.

Otherwise, you really can't tell if the intent is to say January or December, in this case.