formula

[rhr9357]

if cell#b2 has the word MCLANE and any words or numbers after that then enter the value of cell c2

 

[MrKowz]

Edit: Misread your question:

<b>Excel 2003</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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Joe McLane 3219312839</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Jon Doe ds123</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">rjdfaMcLane1236dsfa</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">dsa McLane</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</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">D1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">"McLane",B1</font>)</font>),C1,""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[rhr9357]

If cell#b2 has the word MCLANE and any other words or numbers after it then enter the value of cell # c2

 

[MrKowz]

This what you need?

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>Joe McLane 3219312839</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Jon Doe ds123</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>rjdfaMcLane1236dsfa</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>dsa McLane</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D2</TH><TD style="TEXT-ALIGN: left">=IF(ISERROR(SEARCH("McLane",B2)),"",IF(LEN(RIGHT(B2,LEN(B2)-SEARCH("McLane",B2)-5))>0,C2,""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[rhr9357]

if cell#e1 contains either the words paymentech or American express and any letters or numbers that follow them then enter the value of cell c2