Conditional formatting

[JohnnySmith11]

Hi everyone!
I have a list of email addresses in column A.

A valid address is one that meets all the following conditions:
1. Has the sign "@".
2. At least 2 characters before the sign "@".
3. No spaces.
4. Has at least one dot "."

If the address is valid – it will be shown in blue color.
If the address is not valid – it will be shown in bold red color.

I am so lost. Could anyone please help?

Thanks so much in advance

 

[mart37]

you can use 4 helper-cells to make the conditions with: IF - SEARCH - TRUE - FALSE
Then you can combine these cells in one formula with: IF - AND - TRUE - FALSE

 

[aRandomHelper]

Try:

Book1.xlsm
	A
1	jwnef@fawe.com
2	f@fff.com
3	erewre
4	grge@.com
5	howaboutthis@yahoo.com
6	what about this@google.com
7	12@3.
8	12@3.4
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=AND(MATCH("*??@*?.?*",$A1,0),ISERROR(FIND(" ",A1)),$A1<>"")	text	NO
A:A	Expression	=OR(AND(ISERROR(MATCH("*??@*?.?*",$A1,0)),$A1<>""),NOT(ISERROR(FIND(" ",A1))))	text	NO

 

[JohnnySmith11]

Thank you so much! For some reason, this shows as blue even though it does not have at least two characters before the @.

 

[aRandomHelper]

There could be non-breaking space in there, looks like a space but is actually a different character. A rather common problem when you copypasta web page data into Excel.
Consider removing those characters first.

 

[pjmorris]

try this (excel will naturally format valid email address in Blue):

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10	Expression	=IFERROR(AND(ISERROR(FIND(" ",A2)),FIND("@",$A2)>2,FIND(".",RIGHT($A2,LEN($A2)-FIND("@",$A2)))>0)*1,0)=0	text	NO

 

[JohnnySmith11]

This is what I originally did just using the suggested Excel conditional formatting options:
Also, do you think it's a must to use a custom function like you did above (as opposed to what I did here)?
The one thing I did not manage to achieve using my method was the "at least two characters before the @" rule...

 

[pjmorris]

simple answer is I (almost) always use custom format conditions as they're so much more powerful. But in the right place the built in functions are brilliant.

 

[JohnnySmith11]

simple answer is I (almost) always use custom format conditions as they're so much more powerful. But in the right place the built in functions are brilliant.

I understand... And what simple function would you do just for this rule "At least 2 characters before the sign @"? How do I make an if that does that?

 

[aRandomHelper]

Post #3 already covers that, using match to look for 2 characters before "@".

Post #5 suggests why you still bump into the problem you mentioned in post #4.