Results 1 to 6 of 6

Strip @ and domain name from email address

This is a discussion on Strip @ and domain name from email address within the Excel Questions forums, part of the Question Forums category; I need a macro to take an email address, strip off the "@" and full domain name. The length (and ...

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Strip @ and domain name from email address

    I need a macro to take an email address, strip off the "@" and full domain name. The length (and the domain name) will not be known. Example: jsmith@company.com to become just jsmith.

    This will be used in other macros within my workbook and each entry could be different so a simple ctrl H on the column cannot be used.

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    6,391

    Default Re: Strip @ and domain name from email address

    Something like this?

    Code:
    Sub Email()
    Dim r As Range
    
    For Each r In Selection
    
        r.Value = Left(r.Value, WorksheetFunction.Find("@", r.Value) - 1)
    
    Next r
    
    End Sub
    Neil

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Re: Strip @ and domain name from email address

    Works great for a selection in worksheet - thanks for quick reply.

    But how about coding an entire column "H" and stopping at the last entry?

  4. #4
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    6,391

    Default Re: Strip @ and domain name from email address

    About to go to bed, but give this a whirl
    Dim last_row as long
    dim i as long
    last_row = range("H" & rows.count).end(xlup).row

    for i = 2 to end_row
    with cells(i,8)
    .value = Left(.Value, WorksheetFunction.Find("@", .Value) - 1)
    end with
    next i
    Neil

  5. #5
    Board Regular CWatts's Avatar
    Join Date
    Jan 2010
    Location
    Maine, USA
    Posts
    666

    Default Re: Strip @ and domain name from email address

    Perhaps this:

    Code:
     
    Sub StripEmail()
        Columns("H:H").Replace What:="@*", Replacement:=""
    End Sub

  6. #6
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Re: Strip @ and domain name from email address

    Thank you sir, it works perfectly!

    "old Excel guys don't quit, they just don't remember"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com