find the text from the entire column and mark it in red colour

[nalini.p2007]

Hi,

Looking for a excel formula to find a particular text from the entire column. Please help me ASAP.

Thanks in advance.

Regards,
Nalini P

 

[Aladin Akyurek]

Hi,

Looking for a excel formula to find a particular text from the entire column. Please help me ASAP.

Thanks in advance.

Regards,
Nalini P

A small example would convey your problem a bit better...

 

[nalini.p2007]

Thanks for your reply Aladin. i wanna to find a word "gmail" from the entire column A and make the cell red colour when word is found. Please advice me on this.

<table border="0" cellpadding="0" cellspacing="0" width="221"><colgroup><col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:118pt" height="20" width="157">column A</td> <td class="xl67" style="border-left:none;width:48pt" width="64">Column B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">nalini@gmail.com</td> <td class="xl65" style="border-top:none;border-left:none">gmail</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">rose@hotmail.com</td> <td class="xl65" style="border-top:none;border-left:none">yahoo</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">tina@yahoo.com</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">country@facebook.com</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">priyaX@sun.com</td> <td class="xl65" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Thanks for your reply Aladin. i wanna to find a word "gmail" from the entire column A and make the cell red colour when word is found. Please advice me on this.

<TABLE border=0 cellSpacing=0 cellPadding=0 width=221><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 48pt" width=64></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 118pt; HEIGHT: 15pt" class=xl67 height=20 width=157>column A</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl67 width=64>Column B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl66 height=20>nalini@gmail.com</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>gmail</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl66 height=20>rose@hotmail.com</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>yahoo</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl66 height=20>tina@yahoo.com</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl66 height=20>country@facebook.com</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl66 height=20>priyaX@sun.com</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl65>

</TD></TR></TBODY></TABLE>

Select A2:A5.

Run the following formula in Conditional Formatting:

=SEARCH("@gmail.",$A2)

Apply the desired formatting.