Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Find & Replace problems... at the end of my thread (ha)

4 views
Skip to first unread message

dunndealpr

unread,
Jun 6, 2013, 11:05:29 AM6/6/13
to

Hey guys. I have Excel 2007. I have a spreadsheet with email addresses
in it surrounded by other text. I want to automatically highlight in
yellow each row containing the words gmail, yahoo, hotmail and so on.

I CTRL-H then enter the info as you see it in the pic attached. When I
click Replace All it says "that function is not valid". It works like a
charm for every other word I test it with. It's almost interesting in a
head-exploding kind of way.

Any ideas?


+-------------------------------------------------------------------+
|Filename: Untitled-1.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=882|
+-------------------------------------------------------------------+



--
dunndealpr

Claus Busch

unread,
Jun 6, 2013, 12:27:52 PM6/6/13
to
Hi,

Am Thu, 6 Jun 2013 16:05:29 +0100 schrieb dunndealpr:

> Hey guys. I have Excel 2007. I have a spreadsheet with email addresses
> in it surrounded by other text. I want to automatically highlight in
> yellow each row containing the words gmail, yahoo, hotmail and so on.

your strings in column A. Then try in B1:
=--(COUNT(FIND({"yahoo","gmail","hotmail"},A1))>0)
Enlarge the formula for all other searched providers.
Select A1:Bn => Conditional Formatting => new Rule => use formula ...
and as formula:
=$B1=1 and format with your wished back color.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

dunndealpr

unread,
Jun 6, 2013, 1:47:13 PM6/6/13
to

Claus Busch;1612169 Wrote:
> Hi,
>
> Am Thu, 6 Jun 2013 16:05:29 +0100 schrieb dunndealpr:
> -
> > Hey guys. I have Excel 2007. I have a spreadsheet with email
> addresses
> > in it surrounded by other text. I want to automatically highlight in
> > yellow each row containing the words gmail, yahoo, hotmail and so
> on.-
>
> your strings in column A. Then try in B1:
> =--(COUNT(FIND({"yahoo","gmail","hotmail"},A1))>0)
> Enlarge the formula for all other searched providers.
> Select A1:Bn => Conditional Formatting => new Rule => use formula ...
> and as formula:
> =$B1=1 and format with your wished back color.
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

interesting! wasn't hip to conditional formatting. not hip to formulas
either, but it seems i don't even need them to get what i need done
here. thanks!

it just seems like find and replace should have worked regardless
though. that vexed me to no end.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
dunndealpr

Ron Rosenfeld

unread,
Jun 6, 2013, 3:26:26 PM6/6/13
to
On Thu, 6 Jun 2013 18:47:13 +0100, dunndealpr <dunndealp...@excelbanter.com> wrote:

>it just seems like find and replace should have worked regardless
>though. that vexed me to no end.

It worked fine for me with Excel 2007. Of course, it only highlights the cell, not the entire row.
0 new messages