Vlookup trim???

[Alex O]

Is there a formula that will clean up the lookup value so that "BD" only appears before the first Cont #? i.e. T2 is correct - T3 would reflect BD Cont # 1417546, Cont # 3537603.

Any help is appreciated!

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 623px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>T</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Calibri">BD Cont # 3264331</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 1417546, BD Cont # 3537603</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3793290, BD Cont # 3947030</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3695870</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 4022730</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3738615, BD Cont # 3738620, BD Cont # 3738646</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3980671</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3869473</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3723331</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>T2</TD><TD>=IF(B2="","",VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE))</TD></TR><TR><TD>T3</TD><TD>=IF(B3="","",VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!AO6:AP132,2,FALSE))</TD></TR><TR><TD>T4</TD><TD>=IF(B4="","",VLOOKUP(B4,'Step 2 Output & Scrub Sheet'!AO7:AP133,2,FALSE))</TD></TR><TR><TD>T5</TD><TD>=IF(B5="","",VLOOKUP(B5,'Step 2 Output & Scrub Sheet'!AO8:AP134,2,FALSE))</TD></TR><TR><TD>T6</TD><TD>=IF(B6="","",VLOOKUP(B6,'Step 2 Output & Scrub Sheet'!AO9:AP135,2,FALSE))</TD></TR><TR><TD>T7</TD><TD>=IF(B7="","",VLOOKUP(B7,'Step 2 Output & Scrub Sheet'!AO10:AP136,2,FALSE))</TD></TR><TR><TD>T8</TD><TD>=IF(B8="","",VLOOKUP(B8,'Step 2 Output & Scrub Sheet'!AO11:AP137,2,FALSE))</TD></TR><TR><TD>T9</TD><TD>=IF(B9="","",VLOOKUP(B9,'Step 2 Output & Scrub Sheet'!AO12:AP138,2,FALSE))</TD></TR><TR><TD>T10</TD><TD>=IF(B10="","",VLOOKUP(B10,'Step 2 Output & Scrub Sheet'!AO13:AP139,2,FALSE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[MrKowz]

Try:

=IF(B2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE),"BD","$$",1),"BD ",""),"$$","BD"))

 

[morleyuk]

Try this:

=IF(B2="","",LEFT(VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE)),FIND(",",VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE)),1)-1)

Without the Vlookup the formula looks like this: =LEFT(B2,FIND(",",B2,1)-1)

 

[Alex O]

Thanks guys.....both options achieved the desired outcome!

 

[Alex O]

I was just informed that sometimes the "Cont #" will be preceeded <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
By a PD instead of a BD….any way to tweak either formula to accommodate this variable?<o></o>

Thanks

 

[morleyuk]

Alex

Mine works on finding the , so what's before it does not matter.

If the , is not there it will fall over.

 

[MrKowz]

In a single formula:

=IF(B2="","",LEFT(VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE),3)&SUBSTITUTE(SUBSTITUTE(RIGHT(VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE),LEN(VLOOKUP(B2,'Step 2 Output & Scrub Sheet'!AO5:AP131,2,FALSE))-3),"BD",""),"PD",""))

Although that is going to be an incredibly ineffecient formula, as it would have to perform the VLOOKUP several times. I would reccommend using the VLOOKUP returned value as a helper cell, and using the following formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">BD Cont # 3264331</td><td style="border-left: 1px solid black;;">BD Cont # 3264331</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">PD Cont # 1417546, PD Cont # 3537603</td><td style="border-left: 1px solid black;;">PD Cont # 1417546,  Cont # 3537603</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 3793290, PD Cont # 3947030</td><td style="border-left: 1px solid black;;">BD Cont # 3793290,  Cont # 3947030</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 3695870</td><td style="border-left: 1px solid black;;">BD Cont # 3695870</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 4022730</td><td style="border-left: 1px solid black;;">BD Cont # 4022730</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 3738615, BD Cont # 3738620, PD Cont # 3738646</td><td style="border-left: 1px solid black;;">BD Cont # 3738615,  Cont # 3738620,  Cont # 3738646</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 3980671</td><td style="border-left: 1px solid black;;">BD Cont # 3980671</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 3869473</td><td style="border-left: 1px solid black;;">BD Cont # 3869473</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">BD Cont # 3723331</td><td style="border-left: 1px solid black;;">BD Cont # 3723331</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,3</font>) &SUBSTITUTE(<font color="Blue">SUBSTITUTE(<font color="Red">RIGHT(<font color="Green">A1,LEN(<font color="Purple">A1</font>)-3</font>),"BD",""</font>),"PD",""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Alex O]

Thanks MrKowz....your solution worked great.

Colin - I'm still testing your suggestion, but keep getting the "formula contains an error" message. Not sure what the problem is???