Parse first name space last name from email address containing first dot last name

[MitchR]

Howdy folks,
I am living the Access Dream as always but, I am in need of some helping hands here. I am looking to find a way to obtain First name space Last name from an email address of [e-mail]. My table Status_and_Updates has a column of Email_ADDR and I want to parse out the Firstname space lastname out of the email address. I can parse out the first name with a left function and I have tried and tried Right and Mid functions to parse out the lastname with no success I am affraid

Left([Status_and_Updates].[Email_ADDR],InStr([Status_and_Updates].[Email_ADDR],".")-1)+" "+Mid Function

I have come very close but I am not able to complete the function to parse just the last name from between the dot and the "@."

[NeoPa]

Hi Mitch.

You don't indicate whether this is in a query or in VBA. I'll start with a Public Function and you can say whether that works for you or whether the question needs to be amended :

Public Function GetNameFromSMTP(ByVal strSMTP As String) As String
    strSMTP = Split(strSMTP, "@")(0)
    GetNameFromSMTP = Split(strSMTP, ".")(0) & " " & Split(strSMTP, ".")(1)
End Function

[MitchR]

Hi NeoPa,
Thank you so much in helping me with a solution. I am so sorry, I was just a little frustrated at the time I wrote my post. The answer to the question is that my formula is from a Query. I was looking for a way to parse the data from within the query itself.

[NeoPa]

Well, as you'll probably have guessed, that's a lot more complicated - not to say awkward.

Left([Status_and_Updates].[Email_ADDR],InStr([Status_and_Updates].[Email_ADDR],'.')-1)+' '+Mid([Status_and_Updates].[Email_ADDR],InStr([Status_and_Updates].[Email_ADDR],'.')+1,InStr([Status_and_Updates].[Email_ADDR],'@')-InStr([Status_and_Updates].[Email_ADDR],'.')-1)

Or, made a little more readable in order to understand, we'll replace [Status_and_Updates].[Email_ADDR] with [X] :

Left([X],InStr([X],'.')-1)+' '+Mid([X],InStr([X],'.')+1,InStr([X],'@')-InStr([X],'.')-1)

PS. This means you can copy/paste the latter one then simply do a Find & Replace to turn it into what you need specifically.

[Nauticalgent]

Pardon me for jumping in here, but I thought this might be useful to others.

Why Team Access did not make the Split() Function available in the Query grid is beyond me, but thanks to Daniel Pineault, there is a pretty neat alternative:
<Link removed - feel free to search for it>

[MitchR]

Team,

Thank you so much for everyone helping me!

NeoPa, Your solution was absolutely on point. I cannot say thank you enough. As awkward as the query solution can be, it is absolute gold for my process on this app.

I really appreciate everyone's assistance and patience on this one.

Thank you again!

P.S. Indenting my code has been a tremendous help! Thank you!

[NeoPa]

MitchR:
P.S. Indenting my code has been a tremendous help! Thank you!

Very pleased to hear it Mitch. It's a very important point.

Also pleased to hear that helped. As our NauticalGent has pointed out you can also call code in your project as long as it's declared as Public. That's why my first response could also have helped in this situation had you chosen to go that route.

[NeoPa]

NauticalGent:
Pardon me for jumping in here, but I thought this might be useful to others.

No problem with that my friend.

Unfortunately, particularly as Daniel is a personal friend and fellow Access MVP, I was obliged to remove the link due to the site regulations on linking to other sites that are commercial and not sites provided by the provider of the software itself.

Nevertheless, the point is made. You can set up a wrapper function in your own project as long as it's defined as Public. BTW it's easier to use from a standard module but not absolutely necessary.

In this case though, unless you want a project that uses a variation of Split() from multiple different QueryDefs or other SQL, it makes better sense to provide a function to do the job itself. An example of that is in post #2.

NauticalGent:
Why Team Access did not make the Split() Function available in the Query grid is beyond me

QueryDefs, just like any part of access using SQL or expressions, is clearly not in the VBA environment. Jet/ACE SQL, which runs the SQL in an Access database, has access to The Expression Service (TES). TES has access to one/some of the most basic libraries that Access has, including your own project, but certainly not the VBA one. Split() is found as VBA.Strings.Split() - thus is unavailable to TES even though it's available to your projects VBA code. I hope that explains why MS never made Split() available to use in expressions - even though most of us would love to have it available ;-)