471,893 Members | 1,819 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

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 1021
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,470 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,470 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
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.