Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validate email format? (Excel 2000 SR-1)

    Hi all, I'm relatively new to the macro scene in Excel and I was wondering if there was a way to validate a list of email addresses to make sure they're formatted properly (@, .com, etc...) Ones that have problems with them, like a '2' was typed instead of a '@' would be highlighted somehow... Thanks.

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate email format? (Excel 2000 SR-1)

    This sub should check the cells e1 to e5 on Sheet1 for the "@" and highlight the cell in yellow if not found.

    <pre>Sub checkmail()
    On Error Resume Next
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("e1:e5")
    cell.Interior.ColorIndex = 0
    If IsError(Application.WorksheetFunction.Find("@", cell) = True) Then _
    cell.Interior.ColorIndex = 6
    Next
    End Sub
    </pre>


  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Validate email format? (Excel 2000 SR-1)

    You can also use conditional formatting to do simple checks.

    Select one of the cells in the Range, (say Cell A1)
    Format => Conditional Formatting
    Formula is =FIND("@",A1,1)<LEN(A1)
    (or whatever check you want to make)
    Set the format you want

    Now copy this cell, select the range of cells you want to apply this to and Paste Special - Formats.

    StuartR

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate email format? (Excel 2000 SR-1)

    StuartR gives you a good solution to find, but, this formula is for the opposite thing. Not to find.
    =ISERROR(SEARCH(""@"",A1,1))

    Or you can put both with different format

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate email format? (Excel 2000 SR-1)

    sorry, this is the correct...

    =ISERROR(FIND(""@"",A1,1))

Posting Permissions

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