+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting - formula if cell does NOT contain "," or "@"

  1. #1
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Conditional formatting - formula if cell does NOT contain "," or "@"

    Hi,

    ever since Office 2007 I came to hate the new layout for the conditional formatting... I know have to advise excel a formula affecting the whole sheet (range A2:L5000) to check if cells in column L DO NOT PARTLY contain "@" or "." characters, and then highlight them red. Blank cells should not be affected by this and remain white.

    I tried working around with IF statements, but excel seems to want a TRUE/FALSE formula response...

    Any help please?
    Thanks,
    A2k
    Last edited by Armitage2k; 12-02-2012 at 06:23 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    just so I understand. you want to check to see if a cell does NOT contain either @ or . correct? this is an either/or, not and?

    and just fyi, 2007 CF is MUCH more powerful and far better than earlier versions, and all of them worked on TRUE or FALSE to produce results
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Try these (Select cells A2:L5000):

    Condition 1 (Set to 'Stop if TRUE'): =ISBLANK($L2)=TRUE
    Condition 2: =OR(ISNUMBER(SEARCH("@",$L2)),ISNUMBER(SEARCH(".",$L2)))=FALSE

    Really.. make sure condition 1 is above condition 2 in the list, and click that 'Stop if TRUE' checkmark, otherwise it won't work right.

    - Moo

    EDIT: CHANGED FORMULA ABOVE TO SEARCH FUNCTION... I was testing MATCH before and it wasn't working but forgot to change when I pasted to the forum.. sorry!
    Last edited by Moo the Dog; 11-30-2012 at 09:45 AM. Reason: Modified formula so that it actually works! :)

  4. #4
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Let me clearly explain this.

    I need to check cells with email addresses and want to highlight those entries which do not have a EITHER an "@" symbol or "." in the cell. I tried the wizard with the "Value does not contain" option, but this leads me exactly nowhere and it affects all blank cells.

    Any formula-wise help would be great here.
    Thanks,
    A2k

  5. #5
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    @ Moo the dog
    Thanks, but it now highlights all the cells that have either an "@" or "." in it...

    I had that same issue many times before, thats the reason why I dislike the whole TRUE / FALSE thing

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    did you see my edit above? changed the formula to use SEARCH, instead of MATCH. It works on my end.

    - Moo

    Adjusted formulas:
    Condition 1 (Set to 'Stop if TRUE'): =ISBLANK($L2)=TRUE
    Condition 2: =OR(ISNUMBER(SEARCH("@",$L2)),ISNUMBER(SEARCH(".",$L2)))=FALSE
    Last edited by Moo the Dog; 11-30-2012 at 09:44 AM. Reason: Added modified formulas

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Here is an example workbook with it working.

    - Moo
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    found the problem. I added a $ to the $L2 range since the formula otherwise was checking the whole A:L rang for "@" and ".".

    However, I did find one little problem with your workbook. If you were to enter test@gmailcom (missing ".") or testgmail.com (missing "@"), the conditional formatting would not highlight the cell. If either one is missing, the conditional formatting should be active.

    Thank you so much,
    A2k

  9. #9
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Sorry about that, I had $L2 in the formula in my spreadsheet, but for some reason when I posted the formula here I left it out.

    Regarding your last question about both being required, that's easy enough to fix... change the OR to AND. That way it requires both to be present.

    =AND(ISNUMBER(SEARCH("@",$L2)),ISNUMBER(SEARCH(".",$L2)))=FALSE

    - Moo

  10. #10
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Shorter rule..perhaps

    Please Login or Register  to view this content.
    assuming an email ID would always have "." followed by the "@"
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  11. #11
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Try this:

    =ISERROR(FIND(".",$L2,FIND("@",$L2)))

  12. #12
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Ace and Mama... well done. The formulas you proposed will actually test the data better, since they are looking for @ and then the . whereas my formula just checks to see if either exists in the cell.

    I'm always learning...

    - Moo

  13. #13
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Conditional formatting - formula if cell does NOT contain "," or "@"

    Dont you guys have complexes for being so much smarter than the average excel user? ;D

    Thanks so much for the help, did work out great at last.
    A2k

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1