Separate a string

mrexcelrc

Board Regular
Joined
Oct 14, 2010
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Thanks, that works. But what would be the formulas as I described? It will be much helpful to have a formula> Thanks.
 
Upvote 0
Try these...

Code:
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
 
Upvote 0
Hi your E1 formula is identical to D1 and therefore returns the same value as D1. I need the formula to return the amount.
 
Upvote 0
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...
 
Upvote 0
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"))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top