473,379 Members | 1,252 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 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 1159
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,556 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,556 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

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

Similar topics

1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
3
by: Edmund Wong | last post by:
I have one client fiield that stores First and last name. I would like to separate the names into a lastname field and firstname field. Can anyone tell me how to do this? thanks
1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
0
by: Peter | last post by:
Using CDO 1.21 how do I get First Name and Last Name from Global Address Book? I have the following code where I retrieve LastName and FirstName in one field, but how do I get LastName and...
4
by: John | last post by:
Good Day all I have a single field that has both the first and last names in it. In some records we even have a middle initial. I was thinking of using the Left (Fullname,XX) function to get the...
4
by: rhepsi | last post by:
hii all, i have aproject where... wen i enter a text in the textbox..., accordingly it should search in teh first name and last name of the database and display the records How to search...
0
by: preeti13 | last post by:
Hi friends i am here again with my probelm. i have one login page when first login on the page it is checking the active directory user exist or not if user exist then go the next page all i nee to...
2
dlite922
by: dlite922 | last post by:
I'm reading a CSV file and the firstname and last name are together separated by a space. (There could be middle name or middle initial as well, but is being ignored) Since i'll be doing this...
12
by: mcupito | last post by:
I have a text box where users will enter a first and last name, and then search by those parameters. I can find plenty of examples and have done search functions where first name and last name are...
2
by: hh22 | last post by:
I want to join a patient id from a table to a first name and last name in another table, how can i do that?
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

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.