|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Separate Username in Email By Richard Rost 
5 years ago
Split the Username from Domain in an Email Address In this video, I'll show you how to isolate the username (left) portion of the email address from everything after the @ sign (the domain). Makenna from Seattle, Washington (a Gold Member) asks: I've got a list of email addresses and I need to isolate just the username part from the domain. How can I do that? MembersI'll show you how to separate the domain name, get a count of the number of times a domain name appears in the list, and change one domain name to another.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksCalculated Query Fields: https://599cd.com/Calculated
IntroIn this video, I will show you how to separate the username part from the domain in an email address using Microsoft Access. We'll work with a sample table of email addresses, create a calculated query field, and use the InStr and Left functions to extract just the username section. This quick tutorial is great if you need to pull out everything before the @ sign in your data for reporting or analysis.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to separate the username part from the domain in an email address. Today's question comes from McKenna from Seattle, Washington, one of my Gold members. McKenna says, I've got a list of email addresses and I need to isolate just the username part from the domain. How can I do that? Well, let's take a look at how to do that. Start with a copy of my TechHelp free template. You can download a copy of this from my website. I'll put a link down below. If you take a look at the customer T table, you'll see there's a list of customers, each with an email address. What McKenna wants to do is separate out that username part. She wants everything in front of the at sign. So how do we do that? Well, we're going to use a query to do that. Go to Create, Query Design. We're going to bring in the customer table into our query, just like that. Close the Add Tables list. Now in here, we'll just bring in the email address. Of course, you can bring in whatever other fields you need. Down here, we're going to create a calculated query field. If you've never done a calculated query field before, I've got a video on how to do that. Go watch that video now and then come back to this one. The first thing we're going to do is use the InStr function, which finds the location of a character in a string, to locate the position of that at sign. So here's what it's going to look like. We're going to call the field atpos: and that's going to be InStr, the InStr function in the email address field, just Email, comma, and then inside quotes, the at sign. I'll zoom in so you can see that better. There it is. The new field is called atpos, set it equal to the InStr of Email, comma, and then inside quotes, the atpos character. That's the separator between the username and the domain. Now when I run this query, I get a number. What's that number represent? That's the character position. If you count across, one, two, three, four, five, six, seven, eight. That is the eighth character in that string, in the email string. Down here, there's a few more characters. So that's the eleventh position. Knowing that number, we can use the Left function to take everything to the left of that and separate that into its own field. Go back to Query Design. Next calculated field over here, we'll use the Left function. Let's call this username:. This will be Left(Email, atpos - 1) because we don't want the at character itself. So I want the left seven characters, for example, eight minus one. Let's see what we get now. Look at that. There we go. It's that easy. That's using the InStr function and the Left function. If you actually want to save that value in the table now in a separate field, you could use an update query. If you want, I've got separate videos on how to use update queries. I'll put links to that down below. If you want to learn more about these string functions like Left and InStr, I cover them in my Access Expert 14 class. I'll put a link to that down below. If you want to learn more, in the extended cut of this video, I'll cover more for the members, including separating the domain part out. Yes, there's a Right string function as well. We'll count the number of unique domains so you can see this: I have this many Gmail accounts, I have this many Yahoo accounts, and so on. We'll change the email domain. Let's say you've got a company that's changing its domain name. They want to change from amicron.com to 590.net. I'll show you how to do that. That will be in the extended cut for members. Silver members and up get access to all of my extended cut videos. How do you become a member? Click the Join button below the video. If you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar. And it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the primary goal covered in this video?A. To separate the username part from the domain in an email address B. To sort a list of email addresses alphabetically C. To create new email addresses from scratch D. To encrypt email addresses for security Q2. Which function is used to find the location of the at sign in the email address? A. Left B. InStr C. Right D. Mid Q3. In Access query design, what is the significance of the number returned by the InStr function when used on an email address? A. It gives the length of the email address B. It shows the row number C. It identifies the character position of the at sign D. It counts vowels in the username Q4. After using the InStr function to find the position of the at sign, which function is used to separate out the username? A. Right B. Replace C. Mid D. Left Q5. Why do we subtract one from the atpos value when using the Left function? A. To include the at sign in the username B. To remove the at sign from the username C. To count the number of domains D. To make the username uppercase Q6. If you wanted to store the extracted username in your table, what kind of query would you use? A. Crosstab query B. Update query C. Append query D. Delete query Q7. Where can you find more information or lessons about string functions like Left and InStr? A. Access Expert 14 class B. Access Beginner Level 1 course C. The Code Vault D. Microsoft Word tutorials Q8. What function is mentioned in the extended cut for separating the domain part from the email address? A. Replace B. Right C. Sum D. Lower Q9. What additional benefit do Gold members have according to the video? A. Access to video editing tools B. Download access to sample databases and the Code Vault C. Ability to download YouTube videos D. Unlimited tech support by phone Q10. What is stated as a free resource for everyone interested in learning Access basics? A. Free Access Level 1 course B. Access Expert 14 class C. Download folder for Platinum members D. Live chat with instructors Answers: 1-A; 2-B; 3-C; 4-D; 5-B; 6-B; 7-A; 8-B; 9-B; 10-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone explains how to extract the username portion from an email address in Microsoft Access. I received this question from one of my Gold members, who needed to separate just the username from a list of email addresses, leaving out the domain.To get started, use a copy of my free TechHelp template, available for download on my website. Within the customer table in this template, you will see that each customer has an email address stored. The goal here is to retrieve everything before the at symbol. To accomplish this, I recommend creating a query. In your database, create a new query and add the customer table. Select the email address field and any other fields you require for your output. To actually extract the username, you will need to create a calculated field in your query. For those who have never worked with calculated query fields, I suggest checking out my separate video covering that topic in detail. Begin by using the InStr function, which tells you the position of a specific character within a string. In this case, you want to locate the at sign in the email address. Create a new field and use the InStr function to find the position of the at character in the Email field. When you run the query, you will get a number indicating where the at sign appears in each email address. With that position in hand, you can now use the Left function to retrieve everything to the left of the at sign. Create another calculated field using the Left function, specifying the email address and taking one less than the number returned by InStr, so that the at symbol itself is not included. When you view the results, you will see just the username portion of each email address. If you decide that you actually want to store this extracted username in your table, you can use an update query to do so. I have separate videos that cover how to create and use update queries if you need further guidance. If you are interested in learning more about string manipulation functions such as Left and InStr, these are explained in detail in my Access Expert 14 class. Also, in today's Extended Cut, I go further for members. I demonstrate how to extract the domain portion from the email address as well, using the Right function. You will learn how to count the number of unique domains in your list, so you can see how many users are using Gmail, Yahoo, and so on. I also cover how to change the domain for a group of email addresses, which is helpful if a company decides to update its domain name, such as moving from amicron.com to 590.net. Silver members and above have access to all of my extended cut videos, live training sessions, and more. If you are interested in becoming a member, you can find all the details on my website, including information about Gold and Platinum membership levels. Gold members receive access to a folder containing all the databases I have used in my TechHelp videos, as well as my Code Vault with many handy functions. Platinum members get all the previous benefits plus access to all of my beginner and some expert courses covering not just Access but also topics like Word, Excel, Visual Basic, and more. These free TechHelp videos will continue as long as there is demand, so if you find them useful be sure to like the video and leave a comment. I do read every comment that comes in. Subscribe to my channel to get notified when I post new content. If you want email notifications for new videos, join my mailing list because YouTube no longer sends out email alerts. If you have not taken my free Access Level 1 course yet, I encourage you to do so. It covers the basics of building databases in Access and runs over three hours. Access Level 2 is just one dollar, or free for any YouTube channel member. To have your own question featured in a future video, simply visit my TechHelp page and submit your question. You will find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListSeparating username from domain in email addressesUsing InStr function to locate the at sign in a string Creating a calculated query field in Access Using the Left function to extract the username Combining InStr and Left functions in a query |
||||
|
| |||
| Keywords: TechHelp Access separate email addresses, isolate email, split domain suffix, strip out domain name, extract domain name PermaLink Separate Username in Email in Microsoft Access |
