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...

The domain extracted from an email address by a query

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:

Enter the expresion into the query design grid

When you run the query the new calculated field appears as a column of domain names:

Run the query to display the 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))

Using the SEARCH() function in Excel

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)

Extracting the username from an email address in a query

The query returns the characters to the left of the @ sign

In Excel you would get the same result with:

=LEFT(A1,SEARCH("@",A1)-1)

Using Excel to extract the username from an email address

^ top

Hit Counter