Extract sub string from a string

[oxygn]

Hi I need to develop a macro that extracts substrings from a string like:

IF ‘lookup 1’ < 2008 AND ‘lookup 2’ <> 0 Then ‘lookup 3’ = 0

I am looking for values between quotes ‘ ’. The expected output will be sub strings:

lookup 1
lookup 2
lookup 3

Thanks

 

[Blade Hunter]

You can use the substitute function to determine if a string exists inside a string in a cell. Is this what you are after? Maybe post some sample data.

 

[oxygn]

Hi thanks for the quick reply. With my macro what I am trying to do is to extract subtrings from a cell.

For example in sheet Formulas from cell B1 I want to extract lookup 1, lookup 2, lookup 3 and lookup 4. Then go to sheet Fieds find the lookup values and highlight the cell where lookup values match.

<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=198 border=0 x:str><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7241" width=198><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 149pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" width=198 height=17>Formula</TD></TR><TR style="HEIGHT: 63.75pt" height=85><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 149pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 63.75pt; BACKGROUND-COLOR: transparent" width=198 height=85>IF ‘lookup 1’ = 2008
AND
(‘lookup 2’ <> 0 OR ‘lookup 3’ > 0)
THEN
‘lookup 4’ = “Y”</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 149pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=198 height=51>IF ‘lookup 5’ = 2007
THEN
‘lookup 6’ = “Y”

</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 129pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=172 border=0 x:str><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 129pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" width=172 height=17>To highlight</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17>lookup 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17>lookup 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17>lookup 3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" height=17>lookup 4</TD></TR></TBODY></TABLE>

The problem I am facing is I dont know how to split up the string so that I can extract lookup text that is in between two quotes.

 

[Blade Hunter]

You do it by finding things around it and using them to split the string up using mid like so:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >IF lookup 1 = 2008 AND (lookup 2 <> 0 OR lookup 3 > 0) THEN lookup 4 = Y IF lookup 5 = 2007 THEN lookup 6 = Y</td><td >Lookup</td><td >Start Char</td><td >End Char</td><td >Retreive</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">4</td><td style="text-align:right; ">13</td><td >lookup 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">25</td><td style="text-align:right; ">33</td><td >lookup 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">42</td><td style="text-align:right; ">51</td><td >lookup 3</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=FIND("=",A1)</td></tr><tr><td >E2</td><td >=TRIM(MID<span style=' color:008000; '>(A1,4,FIND<span style=' color:#0000ff; '>("=",A1)</span>-4)</span>)</td></tr><tr><td >C3</td><td >=FIND("(",A1)+1</td></tr><tr><td >D3</td><td >=FIND("<",A1)-1</td></tr><tr><td >E3</td><td >=TRIM(MID<span style=' color:008000; '>(A1,FIND<span style=' color:#0000ff; '>("(",A1)</span>+1,FIND<span style=' color:#0000ff; '>("<",A1)</span>-1-FIND<span style=' color:#0000ff; '>("(",A1)</span>-1)</span>)</td></tr><tr><td >C4</td><td >=FIND("OR ",A1)+3</td></tr><tr><td >D4</td><td >=FIND(">",A1,C4)</td></tr><tr><td >E4</td><td >=TRIM(MID<span style=' color:008000; '>(A1,FIND<span style=' color:#0000ff; '>("OR ",A1)</span>+3,FIND<span style=' color:#0000ff; '>(">",A1,C4)</span>-FIND<span style=' color:#0000ff; '>("OR ",A1)</span>-3)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


Your if formula doesn't look like an Excel formula though, you don't use THEN in an Excel if formula

 

[Peter_SSs]

Hi I need to develop a macro that extracts substrings from a string like:

IF ‘lookup 1’ < 2008 AND ‘lookup 2’ <> 0 Then ‘lookup 3’ = 0

I am looking for values between quotes ‘ ’. The expected output will be sub strings:

lookup 1
lookup 2
lookup 3

Thanks

Welcome to the MrExcel board!

Assuming the strings are in column B and columns C, D, E, ... are available to hold the results, test this in a copy of your workbook.

It also depends on whether those quotes are exactly as you have shown them or are 'straight' quotes. You may need to include the two lines I have commented out instead of the lines immediately above them.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractStrings()<br>    <SPAN style="color:#00007F">Dim</SPAN> RX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, ary <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRng <SPAN style="color:#00007F">As</SPAN> Range, cl <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> RX = CreateObject("VBScript.RegExp")<br>    <SPAN style="color:#00007F">Set</SPAN> myRng = Range("B1", Range("B" & Rows.Count).End(xlUp))<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> RX<br>        .Pattern = "'.+?'"<br><SPAN style="color:#007F00">'        .Pattern = "‘.+?’"</SPAN><br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> myRng<br>            s = cl.Value<br>            <SPAN style="color:#00007F">If</SPAN> .Test(s) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> ary = .Execute(s)<br>                <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ary.Count<br>                    cl.Offset(, i).Value = Replace(ary(i - 1), "'", "")<br><SPAN style="color:#007F00">'                    cl.Offset(, i).Value = Replace(Replace(ary(i - 1), "‘", ""), "’", "")</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> i<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cl<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[oxygn]

Thanks Blade and Peter for your replies. Your solutions worked perfectly.

Kind Regards