Extract first name space last name from email address with first name dot last name

[MitchR]

I need some help folks. I am trying to extract the first name space last name from an email address containing first name dot last name. I was able to complete a work around to extract first name. Then I am to extract the last name but not "subtract" the "@Domain.com from last name portion of the string. I know there is a way to perform this operation with one function.

[e-mail]

I need to combine these two porcesses into one process.

Dim varName as Variant
Dim varNameClr as Variant
 
varNameClr = Left(rs.Email_ADDR, InStr(rs.Email_ADDR, ".") - 1) & " " & Mid(rs.Email_ADDR, InStr(rs.Email_ADDR, ".") + 1)
'returns John [e-mail]
 
varName = Left(varNameclr, InStr(varNameclr, "@") - 1) 
'removes the @domain.com
'Returns John Smith
 
 

[twinnyfo]

MitchR,

This is probably more than I typically offer, but here is a quick and easy solution:

Option Compare Database
Option Explicit
 
Public Function NamesFromEmail(EmailAddress As String) As String
On Error GoTo EH
    Dim strEmail        As String
    Dim intAtSign       As Integer
    Dim strFirstLast    As String
 
    strEmail = EmailAddress
    intAtSign = InStr(strEmail, "@")
    strEmail = Left(strEmail, intAtSign - 1)
    strFirstLast = Replace(strEmail, ".", " ")
    NamesFromEmail = strFirstLast
 
    Exit Function
EH:
    MsgBox "There was an error splitting the E-Mail address!" & vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function

Hope this hepps!

[NeoPa]

One could just use Split() and Replace().

Public Function ParseName(strEmail As String) As String
    ParseName = Replace(Split(strEmail, "@")(0), ".", " ")
End Sub

[PhilOfWalton]

@NeoPa

Very, very elegant

Phil

[twinnyfo]

How does he do that? Oh, to get inside the brain of NeoPa!

[MitchR]

Thank you @twinnyfo and @NeoPa! NeoPa's solution worked like a surgeon's scalpel with a perfect extraction!

[NeoPa]

You're very kind. I should just point out though, that while brief, it doesn't include error handling, which Twinny's did of course.