How Do I Use a Query to Extract the Domain from an Email
Address?
The domain part of an email address is the part that comes
after the "@" sign. So if you extracted the domain from my email
address: martin@fontstuff.com you would get fontstuff.com.
So how can you do the job with an Access query and get a result like
this...
Access has a number of functions for working with text and you can
combine them to get the desired result: The Right() Function
Provide the Right() function with a text string and a
number representing how many characters you want and it will return
that number of characters from the right hand side of the string.
For example, if I knew that I wanted just the rightmost ten
characters I would use the function like this: Right("martin@fontstuff.com",
10) This would return the string tstuff.com which is
ten characters long, but clearly not the right number of characters
for the job in hand. The InStr() Function
I need all the characters to the right of the "@" sign so I need
to find out the position of the "@" sign in the original string.
This useful function will tell you the position of one string (which
can be one or more characters long) inside another string. It works
like this: InStr("martin@fontstuff.com", "@") This
returns the number 7 since the "@" sign is the seventh
character in the email string. The Len() Function
So now I know how many characters to discard from the left side
of the string. In order to make proper use of the Right()
function I need to find out how long the string is. If you provide
the Len() function with a string it will tell you how long it
is: Len("martin@fontstuff.com") This returns the number
20 since the entire email address is twenty characters long.
Build the Expression
Now I can combine these functions to extract the information I
need: Right("martin@fontstuff.com", Len("martin@fontstuff.com")
 InStr("martin@fontstuff.com", "@")) This correctly returns
the string fontstuff.com Putting it into Practice
These text functions are also available in VBA so you can use the
same expression in numerous applications. You will have to replace
the actual email address (and its surrounding quote marks) used in
my examples above with an expression such as a field or variable
name which represents your email string.
In an Access query you would use the expression to create a new,
calculated field. For example, if the field containing the email
addresses was called "Email" and you wanted to call your calculated
field "Domain" you would enter: Domain: Right([Email],
Len([Email])  InStr([Email], "@")) ...in the Field row
of the query design grid as shown below:
When you run the query the new calculated field appears as a column
of domain names:
These text functions are also available in VBA so you can use the
same expression in numerous applications. Excel doesn't have the
InStr() function. Instead it uses the similar SEARCH()
function. If you had an email address in cell A1 for example,
your formula would look like this: =RIGHT(A1, LEN(A1) 
SEARCH("@",A1))
So How Do I Get the UserName?
That's even easier! In the same way, use the InStr()
function (or in Excel the Search() function) to find the
position of the "@" sign and use the number it returns (minus 1) to
tell the Left() function how many characters to return. In
Access your expression would look like this: UserName:
Left([Email],InStr([Email],"@")1)
In Excel you would get the same result with:
=LEFT(A1,SEARCH("@",A1)1)
