Split Email Address after / before @ (1 Viewer)

[abbaddon223]

Hello experts, can you help please@

I have a field with a value in, test.test@hotmail.com

What I'd like to do is get the values before the @ an after the @ into two different fields so I have:

[Before] [Email] [After]
test.test test.test@hotmail.com hotmail.com

Can anyone help please?

 

[Frothingslosh]

You will need to use Left(), Right(), InStr(), and/or InStrRev().

 

[abbaddon223]

Hi, thanks for the reply - what I'm struggling with is the syntax rather than the function.

 

[Frothingslosh]

The way I'd probably go about it is to use InStr() to locate the position of the @ symbol.

lngDivider= InStr(First position to be searched - starts at 1 if left blank,String to be searched, String to search for, Comparison type - I recommend vbTextCompare)

Example would be

lngDivider = InStr(1, strEmail, "@", vbTextCompare)

Once you have that, you can use Left(String to be searched, lngDivider - 1) to get everything before the @, and Right(String to be searched, lngDivider - 1) to get everything after the @.

 

[abbaddon223]

Hi, woudl this work in a query or is it VBA?

Thank you!

 

[Frothingslosh]

It can be done either way. In SQL, you'd do something like this:

SELECT aaaTest.Email, Left([Email],InStr(1,[Email],"@")-1) AS AddyName, Right([Email],Len([Email])-InStr(1,[Email],"@")) AS EmailDomain FROM aaaTest;

Normally, though, I just create a custom function that does the same thing, and have it called via an unbound control on the form or report, mainly because I try to put as few calculations as possible into queries to keep them as fast as possible.