469,125 Members | 1,780 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

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

MitchR
65 64KB
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

6 855
twinnyfo
3,653 Expert Mod 2GB
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
32,161 Expert Mod 16PB
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
1,430 Expert 1GB
@NeoPa

Very, very elegant

Phil
Jun 8 '18 #4
twinnyfo
3,653 Expert Mod 2GB
How does he do that? Oh, to get inside the brain of NeoPa!
Jun 8 '18 #5
MitchR
65 64KB
Thank you @twinnyfo and @NeoPa! NeoPa's solution worked like a surgeon's scalpel with a perfect extraction!
Jun 8 '18 #6
NeoPa
32,161 Expert Mod 16PB
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.

Similar topics

1 post views Thread by Prasad Karunakaran | last post: by
3 posts views Thread by Edmund Wong | last post: by
reply views Thread by Peter | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.