Searching rows for multiple partial terms 2013

[cfitz23]

Hi All,

I have a workbook with close to 150 sheets in it. Each sheet only has 2 columns, A = email address B = Active/Inactive member.

What I am trying to do is search A for multiple different terms, highlighting cells that do not match a specific partial text.

To explain it further - the email address could contain a number of different domains - (up to 20). So I am trying to basically use the text that comes after the @ sign in the address, if the text does not contain one of the domains then the cell will be highlighted as invalid.


I have a fairly poor attempt here
=IF(OR(ISNUMBER(SEARCH("test@hello.com.au",A2)),ISNUMBER(SEARCH("@admin.hello.com.au",A2))),"Valid","Invalid")

the problem as you can see is I only have two strings and im Sure excel is searching all letters in the string individually, not as a whole string.

I would be very grateful if I cold get some help. Also I do not mind applying the formula to each of the 150+ sheets but if there were a way to search "all column A's" in the work book this would be awesome!!

Thanx 23

 

[Oeldere]

g1 = test@hello.com.au

h1=right(G1,LEN(G1)-search("@",G1,1))

h1 give as result => hello.com.au


After that you can use VLookup to find that result (in h1) in a table.

 

[cfitz23]

g1 = test@hello.com.au

h1=right(G1,LEN(G1)-search("@",G1,1))

h1 give as result => hello.com.au


After that you can use VLookup to find that result (in h1) in a table.

Does this mean I would have to apply the formula 20 times in Cells I1,J1,K1 etc..., one for each of the domains?
Also will this not conflict if some of the domains by coincidence contain the same amount of characters? this could mean @hello.com.au would be correct but @jello.com.au would also be correct when it isn't a valid email address according to my "audit" conditions...?

Thanks for the help by the way, it is hugely appreciated

 

[Oeldere]

After that you can use VLookup to find that result (in h1) in a table.

See the above comment in #2.

If you show a small example of your data, it is easier to explain.

 

[Andrew Poulsom]

You can use wildcards in SEARCH:

=IF(OR(ISNUMBER(SEARCH("*test@hello.com.au",A2)),ISNUMBER(SEARCH("*@admin.hello.com.au",A2))),"Valid","Invalid")