Strip @ and domain name from email address

[trovello]

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.

 

[njimack]

Something like this?

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

 

[trovello]

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?

 

[njimack]

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

 

[CWatts]

Perhaps this:

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

 

[trovello]

Thank you sir, it works perfectly!

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