Mid = Right
By Richard Rost
2 years ago
Using Mid to Extract Right Side of a String in Access
In this Microsoft Access tutorial, I will show you how to use the Mid function to extract the right side of a string, an easier alternative to the traditional Right function. Learn how to handle variable string positions, split full names, and simplify your data manipulation processes.
Members
There is no extended cut, but here is the database download:
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!
Prerequisites
Recommended Courses
Usage
- SpacePos: InStr([FullName]," ")
- FirstName: Left([FullName],[SpacePos]-1)
- OldLastName: Right([FullName],Len([FullName])-[SpacePos])
- NewLastName: Mid([FullName],[SpacePos]+1)
Keywords
TechHelp Access, Mid Function Microsoft Access, Right Side String Access, Mid Function vs Right Function, Extract Last Name Access Query, String Manipulation Access, Access String Functions Tutorial, Full Name Split Access, Text Functions Access Database, Access Data Import Full Name, String Length Position Access
Subscribe to Mid = Right
Get notifications when this page is updated
Intro In this video, I will show you how to use the mid-string function in Microsoft Access to extract the last part of a string, making it even easier than using the right function. We'll talk about basic string functions like left, right, mid, length, and in-string, create a query to split full names into first and last names, and see how the mid function can simplify last name extraction in a real-world example.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we got one for the beginners, and it's surprisingly something I just learned myself. And that's how you can use the mid-string function, just like the right function, which totally blew my mind when I saw this a couple of days ago. Let me give you some background.
Okay, I've been working with string functions since I was a child, literally. I started programming in BASIC on my Cocoa, my Tandy Radio Shack Color Computer, back in the 80s. And I've been using left, right, mid, length, and in-string since I can remember. I've always used left to get the left X number of characters from a string; right to get the right X most number of characters from a string; mid to get stuff in the middle (you go in three characters and give me the next three characters in the middle of the string); length to go into the whole length of the string, and in-string to find the position of something inside that string. All right? And that's pretty much it. Go watch this video for more details on how all of these guys work.
Well, what I didn't realize was it's actually easier to get the right side of the string using the mid function than it is to use the right function. Now, let me show you.
All right, let's say you got a table or you import some data, whatever, where you've got a full name in one field and you want to separate them. Okay? Now, obviously, if you've got a string where you know the position of where you want to separate it, like it's always the fifth character, then yeah, it's easy to use the right string function to get the right four characters. But if it's different, if it varies like here, then it's actually easier to use mid. Let me show you what I mean.
So let's create a query, and this query will be based on that full name table. I'll bring in the full name. And if you look at it, now we've got to figure out where in that full name the space is. And yes, this is a very simple example. It only works with names that have one space in them. If you get a name like Jean-Luc Picard or Eddie Van Halen, it's not going to work. Just bear with me. I'm just trying to make a point. Okay?
Now, to find that space, we can use the in-string function. So, the space position is going to be in-string, the full name field, comma, and then a space like that. That'll say, give me the position of where that space character is. And if I run this now, there you go. In my name, it's in the eighth position. Down here, it's in the fourth position and so on.
Now, to pull out the left X characters of the first name, we just grab the left X minus one characters. So in here, to find the first name, we'd go first name is the left of full name, comma, space position minus one. And now if I run this, there you go, there's the first name, okay?
Now, traditionally, what I used to do to get the last name is you'd have to figure out the length of the entire string and then subtract the space position. So it used to look like this: The last name would be the right of full name, comma, the length of full name minus space position. That'll figure out the entire length of the string and then subtract that space position, and that will give you the right number of characters. And that works. That's what I've been doing for years.
But it's actually easier. All you've got to do is say, give me the right X characters from the space position and add one to it. Watch, it's really, it's weird. Watch. We'll do last name two over here. Watch. Oh, someone's beaming in. All right, we'll say last name two. It's going to be mid full name, comma, space position. That's the space. Plus one.
Now, normally, I always thought since I was a child, I always thought you had to have that third parameter. How many over do you want to go? But if you don't specify that, it just goes to the end of the string. Hit OK and watch this. Mind blown. It basically says, start at that character, that space position character, and if you don't specify the next parameter, it just says, give me from that spot to the end of the line, the end of the string.
I saw this. I'm like, what is going on? This goes against everything I've ever known about mid, and some of you probably know this already. I did not. So, I immediately had to make a video and share this with all of you guys. I'm 51 years old, I've been programming in BASIC since I was probably eight years old, and I just learned this. So, don't feel bad because even I still learn the simplest things that just are like, what? That blew my mind.
So if you already knew that, I want to hear from you. Just comment down below and tell me. Say, Rick, what, have you been sleeping for these 30-some years?
OK, well, that's it. That's all. I've got lots more lessons like this on my website. Come check it out. You'll find links down below. That's going to be your TechHelp video for today. Hope you learned something. I know I did. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
TOPICS:
Using the mid-string function like the right function
Creating a query based on a full name table
Finding the position of a space in a string using in-string
Extracting the first name using the left function
Calculating the length of a string
Extracting the last name using the right function
Simplifying last name extraction using the mid function
Understanding default parameters in the mid function
Applying mid function to string manipulation in a query
COMMERCIAL:
In today's video, I will show you how to use the mid-string function in a way that makes it easier to get the last part of a string compared to the right function. We'll start by understanding basic string functions like left, right, mid, length, and in-string. Then, we'll create a query to split a full name into first and last names by finding the position of the space character. You'll see why using the mid function for the last name can be simpler than you think. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What function did Richard discover can also be used like the right function?
A. Strings.left
B. Strings.len
C. Strings.mid
D. Strings.instr
Q2. What programming language did Richard start working with as a child?
A. Python
B. BASIC
C. C++
D. JavaScript
Q3. What example did Richard use to demonstrate the mid function?
A. Calculating the length of a string
B. Counting characters
C. Splitting a full name into first and last names
D. Finding a character in a string
Q4. According to Richard, what function is used to find the position of a character in a string?
A. left
B. mid
C. len
D. instr
Q5. What does the expression instr("full name", " ") return?
A. The last character of the string
B. The total length of the string
C. The position of the space character in the string
D. The substring starting from the space character
Q6. How did Richard used to get the last name from a full name string?
A. Using the mid function
B. Using the instr function
C. Using the left function
D. Using the right function along with len
Q7. What realization did Richard have about using the mid function?
A. It requires a third parameter
B. It can be used without a third parameter
C. It cannot handle spaces in strings
D. It can only be used for calculating lengths
Q8. What additional step is necessary when using the mid function to extract the last name starting from the space position?
A. Specify the number of characters to extract
B. Add one to the space position
C. Use the len function before mid
D. Use the left function in combination
Q9. What phrase did Richard use to describe his reaction to the mid function discovery?
A. Excited and surprised
B. Confused and disheartened
C. Mind blown
D. Doubtful and skeptical
Q10. Who is the diamond sponsor mentioned at the end of the video?
A. Richard Rost
B. Jean-Luc Picard
C. Eddie Van Halen
D. Juan Soto
Answers: 1-C; 2-B; 3-C; 4-D; 5-C; 6-D; 7-B; 8-B; 9-C; 10-D
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.Summary Today's TechHelp tutorial from Access Learning Zone is focused on an interesting way to use string functions in Microsoft Access. This one is especially useful for beginners, but even after decades of programming, I learned something new while preparing this lesson.
I have been working with string functions like Left, Right, Mid, Len, and InStr since my early days programming in BASIC back in the 1980s. Traditionally, I've used Left to retrieve a certain number of characters from the beginning of a string, Right to get characters from the end, and Mid to extract characters from a specific position in the middle. Len gives you the string's total length, while InStr finds the position of one string inside another. If you want more information or a refresher on the basics of these functions, there are detailed videos on my website.
My usual method for splitting a full name field into first name and last name in Access has always been straightforward. Suppose you have a table with a single field for the full name and you want to separate it into first and last names. If you already know where the break should occur, such as always after the fifth character, then using the Right function is easy. However, names do not always have a consistent structure, which complicates things.
To tackle this, you can use InStr to find the position of the space character in your full name. This method assumes, for simplicity's sake, that there is only one space in the name. So, for example, if your data has "John Smith," InStr returns where that space occurs.
You can then extract the first name by using Left to pull the number of characters up to, but not including, the space. For example, you get the Left of FullName, with the number of characters set to the space position minus one.
For the last name, my old approach was to use the Right function, calculating how many characters to grab by subtracting the space position from the total length of the full name. This process works, but it involves extra calculation.
What surprised me recently is that the Mid function actually makes it easier to extract the last name. If you use Mid with the full name, starting at the position just after the space, and simply omit the third parameter that specifies the length, it will automatically return everything from that point to the end of the string. There is no need to calculate the length or subtract positions. Just specify the starting point, and Mid takes care of the rest.
This was a revelation to me, even after many years of programming. I always believed you needed all three arguments with Mid, but leaving off the length parameter lets you capture the remaining part of the string with no extra work. If this is new to you as well, you are not alone. It just goes to show that there is always something new to learn, no matter how experienced you are.
If you already knew this trick, feel free to let me know how long you have been taking advantage of it. I always enjoy hearing from others who have been using these functions in clever ways.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Using the mid function to extract the right part of a string
Creating a query to split full names
Finding the space position in a name with in-string
Extracting the first name using the left function
Extracting the last name using the right function
Extracting the last name using the mid function
Omitting the third parameter in mid function to get to end of string
Applying string functions in a query to separate names |
