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]

See Text to Columns:

http://support.microsoft.com/kb/214261

 

[mrexcelrc]

Thanks, that works. But what would be the formulas as I described? It will be much helpful to have a formula> Thanks.

 

[Rick Rothstein]

Try these...

B1:  =LEFT(A1,FIND("_",A1&"_")-1)
 
C1:  =--TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(1),LEN(A1))),"0-00-0000")
 
D1:  =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(2),LEN(A1)))
 
E1:  =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(2),LEN(A1)))

You will need to format the cells in Column C using this Custom Format pattern.... dd-mmm-yyyy

 

[mrexcelrc]

Hi your E1 formula is identical to D1 and therefore returns the same value as D1. I need the formula to return the amount.

 

[mrexcelrc]

Hi there any comments to my last question?

 

[NateO]

Notice a trending change in the functions? Try changing E1 to multiply by 3 instead of 2.

Still, why not use Text to Columns? It's vastly more efficient, and you won't have to worry about syntax that might not be transparent to you...

 

[NateO]

You might end up with a problem in C1, too. Try this:

=DATEVALUE(TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*1,LEN(A1))),"0-0-0000"))

 

[mrexcelrc]

Perfect!. Thank you

 

[Rick Rothstein]

You might end up with a problem in C1, too. Try this:

=DATEVALUE(TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*1,LEN(A1))),"0-0-0000"))

I missed the December 1st date, but I don't think your formula is the answer as it will fail for a date like 11222011 (that is 11/22/2011). The problem is the OP's decision to recognize 1212011 as December 1, 20011 instead of January 21, 2011. The only way I can see that being a valid decision is if the dates are always the first of the month, but we have not been told that is the case.