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.

John.Smith@Domain.com

I need to combine these two porcesses into one process.

Expand|Select|Wrap|Line Numbers

 
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 Smith@Domain.com
 
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:

Expand|Select|Wrap|Line Numbers

 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().

Expand|Select|Wrap|Line Numbers

 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.

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

✓ answered by NeoPa

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ