423,113 Members | 1,976 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,113 IT Pros & Developers. It's quick & easy.

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

MitchR
P: 60
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
  1. Dim varName as Variant
  2. Dim varNameClr as Variant
  3.  
  4. varNameClr = Left(rs.Email_ADDR, InStr(rs.Email_ADDR, ".") - 1) & " " & Mid(rs.Email_ADDR, InStr(rs.Email_ADDR, ".") + 1)
  5. 'returns John Smith@Domain.com
  6.  
  7. varName = Left(varNameclr, InStr(varNameclr, "@") - 1) 
  8. 'removes the @domain.com
  9. 'Returns John Smith
  10.  
  11.  
Jun 6 '18 #1

✓ answered by NeoPa

One could just use Split() and Replace().
Expand|Select|Wrap|Line Numbers
  1. Public Function ParseName(strEmail As String) As String
  2.     ParseName = Replace(Split(strEmail, "@")(0), ".", " ")
  3. End Sub

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 100+
P: 2,499
MitchR,

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function NamesFromEmail(EmailAddress As String) As String
  5. On Error GoTo EH
  6.     Dim strEmail        As String
  7.     Dim intAtSign       As Integer
  8.     Dim strFirstLast    As String
  9.  
  10.     strEmail = EmailAddress
  11.     intAtSign = InStr(strEmail, "@")
  12.     strEmail = Left(strEmail, intAtSign - 1)
  13.     strFirstLast = Replace(strEmail, ".", " ")
  14.     NamesFromEmail = strFirstLast
  15.  
  16.     Exit Function
  17. EH:
  18.     MsgBox "There was an error splitting the E-Mail address!" & vbCrLf & vbCrLf & _
  19.         "Error: " & Err.Number & vbCrLf & _
  20.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  21.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  22.     Exit Function
  23. End Function
Hope this hepps!
Jun 6 '18 #2

NeoPa
Expert Mod 15k+
P: 30,909
One could just use Split() and Replace().
Expand|Select|Wrap|Line Numbers
  1. Public Function ParseName(strEmail As String) As String
  2.     ParseName = Replace(Split(strEmail, "@")(0), ".", " ")
  3. End Sub
Jun 7 '18 #3

PhilOfWalton
Expert 100+
P: 1,257
@NeoPa

Very, very elegant

Phil
Jun 8 '18 #4

twinnyfo
Expert Mod 100+
P: 2,499
How does he do that? Oh, to get inside the brain of NeoPa!
Jun 8 '18 #5

MitchR
P: 60
Thank you @twinnyfo and @NeoPa! NeoPa's solution worked like a surgeon's scalpel with a perfect extraction!
Jun 8 '18 #6

NeoPa
Expert Mod 15k+
P: 30,909
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.
Jun 9 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.