How do I extract data within quotations?

[cfa_nyc]

Since there are multiple sets with double quotations, can anyone tell me how to extract the No inside the double quotations following EMT?

Region is "USD" Reinvestment period is 1 full month and Detail: EMF is "Yes" and Detail: EMT is "No" and Detail: IF is "Yes"

 

[Scott Huish]

If the rest of the sentence always reads like that, perhaps:

=TRIM(SUBSTITUTE(MID(A1,FIND("""",A1,FIND("EMT is",A1)),5),"""",""))

 

[cfa_nyc]

Thanks! I thought I had solid excel skills until you came along - seriously, I've wrestled with this problem for 2 days and couldn't get it to work. I was using all kinds of derivations of MID($A2,FIND("EMT",$A2)+8,FIND("""",$A2,FIND("""",$A2)+1)-FIND("""",$A2)-2) but to no avail.

Thanks again - very much appreciated!

 

[Scott Huish]

You're welcome, glad I could help!

 

[cfa_nyc]

HotPepper-

I have a couple more quick questions and I'd be very grateful if you could help. First, why did you use trim, and then substitute? It clearly works, but I can't quite figure out the logic. Also, below is a slightly different take on my earlier question.

How can I extract "ABR", "MAR", "O", "PI", "ILB", "HY", "SP" after Strategy2 is not, from the following string:

Detail: Region is "USD" Reinvestment Period is until first day after 1 full month and Detail: Currency is "USD" and Detail: Strategy2 is not "ABR", "MAR", "O", "PI", "ILB", "HY", "SP" Reinvestment Period is until first day of full month

thanks in advance!

 

[Peter_SSs]

If your data varies in structure it is a good idea to give more sample data and expected results than just one. If the text you are trying to extract is always just followed by " Reinvestment Period is until first day of full month" then try

=REPLACE(LEFT(A1,LEN(A1)-53),1,SEARCH("Strategy2 is not",A1)+16,"")

Otherwise try

=REPLACE(REPLACE(A1,FIND("#",SUBSTITUTE(A1,"""","""#",LEN(A1)-LEN(SUBSTITUTE(A1,"""","")))),LEN(A1),""),1,SEARCH("Strategy2 is not",A1)+16,"")

 

[Peter_SSs]

Another option may be to use a user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formulas as shown in the screen shot below and copy down.

Of course you would only need one of these. Which one again comes back to what your data is like. For example the result in B3 is probably not what you want but then your data may never be like A3. However, if it could be, the second UDF (column C) would cope with that.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Strategy2(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, stp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Marker <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Strategy2 is not "<br>    <br>    strt = InStr(1, s, Marker, 1)<br>    <SPAN style="color:#00007F">If</SPAN> strt > 0 <SPAN style="color:#00007F">Then</SPAN><br>        strt = strt + Len(Marker)<br>        stp = InStrRev(s, """")<br>        <SPAN style="color:#00007F">If</SPAN> stp > strt <SPAN style="color:#00007F">Then</SPAN><br>            Strategy2 = Mid(s, strt, stp - strt + 1)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> Strat2(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>    <SPAN style="color:#00007F">Const</SPAN> Marker <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Strategy2 is not "<br><br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Pattern = Marker & "(""[A-Z]+"", )*(""[A-Z]+"")(?= )"<br>            .IgnoreCase = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> RegEx.test(s) <SPAN style="color:#00007F">Then</SPAN><br>        Strat2 = Replace(RegEx.Execute(s)(0), Marker, "", 1, 1, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Excel Workbook
	A	B	C
1	Detail: Region is "USD" Reinvestment Period is until first day after 1 full month and Detail: Currency is "USD" and Detail: Strategy2 is not "ABR", "MAR", "O", "PI", "ILB", "HY", "SP" Reinvestment Period is until first day of full month	"ABR", "MAR", "O", "PI", "ILB", "HY", "SP"	"ABR", "MAR", "O", "PI", "ILB", "HY", "SP"
2	Detail: Region is "USD" Reinvestment Period is until first day after 1 full month and Detail: Currency is "USD" and Detail: Strategy3 is not "ABR", "MAR", "O", "PI", "ILB", "HY", "SP" Reinvestment Period is until first day of full month
3	Detail: Region is "USD" Reinvestment Period is until first day after 1 full month and Detail: Currency is "USD" and Detail: STRATEGY2 is NOT "ABR" Other Data "ABC"	"ABR" Other Data "ABC"	"ABR"
Sheet1

 

[Scott Huish]

I have a couple more quick questions and I'd be very grateful if you could help. First, why did you use trim, and then substitute? It clearly works, but I can't quite figure out the logic.

My formula works on the premise that what follows EMT is
is going to be "Yes" or "No" followed by a space

It is going to start looking for the quotation marks starting from the point where it find EMT is

then it is going to extract from the string from this point for 5 characters

this will return "Yes" (5 characters) or "No" followed b a space (5 characters)

Now we are going to take away the quotes with SUBSTITUTE replacing the quotes with nothing

With the YES that would return 3 characters but with the No that would return a No followed by a space, thus the use of TRIM

 

[cfa_nyc]

Thanks so much to all for the assistance - this is extremely helpful information and it will be utilized!