In this tutorial I will show you how to use the MID, LEFT, and RIGHT functions in Microsoft Excel. These are sometimes called the string functions. And what they're often used for is to extract bits of information from existing text or numbers.
So you can see here in this spreadsheet I have a contacts list. Lots of names. and dates of when these names were added to the list. We have addresses, phone numbers, etc. So it's a contact sheet.
But for this example, I would like to extract the area codes from the phone numbers and have them in a separate column. And I'd like to extract some other information as well. And I'm going to do it by using the left, right, and mid functions. So let's first look at area code.
Here we have a column full of phone numbers. And I would like to extract just... the area codes.
To do that, all I have to do is click on cell E2 in this case and type equals. So this cell equals, and then I'll use the left function. I type in the left parenthesis, and you can see the parameters that Excel is looking for here.
It wants some text to examine, and then it wants to know the number of characters to produce. So the text to examine is this phone number here. So I'll just click there on D2. Of course I could have just typed it in as well. And then I'll put a comma and then the number of characters to produce.
Now if you look at the phone number and the area code, in order to get the area code counting from the left, how many characters do I want to capture? Well the left parenthesis counts as one. So that's one, two, three, four, and five.
So I want five characters to be produced. And then I should put in the right parenthesis, tap enter on the keyboard, and look what it did. It examined this data, this phone number here, and pulled out the first five characters in that cell.
So now I get to do that again, right? For Colin. Well, actually no, I don't have to do that again. I can just click on the cell where the formula has been entered, and then double click on the autofill handle that's in the lower right corner.
You can also click and drag. but double-clicking generally is the fastest, best way to use the autofill handle. So you can look up and down this sheet and see that I have now successfully extracted the area code from these phone numbers and put them in this column here, column E.
And so really that's all there is to the left function. It allows you to basically copy characters counting from the left. Let's try another example. This time we'll use the right function.
So here in the addresses, I would like a list of just the zip codes. That way I could easily use the sort options in Excel to sort based on zip code. I could know who's in which state and they would all be grouped together.
So this could be really useful. I'll just type in equals and this time I'm going to use the right function. Now it's probably obvious why I'm using the right function rather than the left.
Because the zip code is here at the right, It just makes sense to use the right function and to count from the right to the left. Okay, so resuming my formula, it's equals, right, left parenthesis. Again, Excel is looking for text to examine and the number of characters to produce. So I want it to examine the text in C2. I put in a comma and zip codes have five characters or digits.
So I'll just put five. and then right parenthesis, tap enter on the keyboard, and I've successfully extracted just the zip code out of this full address. I can click on F2, double click on the autofill handle, and it will automatically identify and pull out the zip codes from column C. Let's look now at one more function in this category, and that is the MID function. And this is the hardest of the three, but it's still very doable.
So here in column G, I would like to produce... a list of the months when these people were added to the list. And you can see that the months are in the middle of this data. They're not at the left, they're not at the right, they're somewhere in the middle.
So this is a little bit harder case. I can't use the left function because that would include the word Wednesday or Sunday. And I can't use the right function because that would include the year. And in order to get to the month, I would probably have to include the date as well. So instead, we're going to use mid.
So this cell equals mid, left parenthesis. Now look at the parameters that Excel needs me to provide. It wants to know what text to examine.
Well, that's going to be easy. It's going to be B2, and I'll go ahead now and click on that, and then the start number, and then the number of characters to produce. So this cell is equal to mid, left parenthesis, B2, comma, now the start number. So what is the number? of the character that I want to begin counting from.
So I want to begin counting at the J. So let's count through. The W is 1, then we have 2, 3. The comma is 4. The space is 5. So if I want to start with J, the start number would be 6. So I'll type in 6, and then another comma.
Now the number of characters. How many characters do I want it to produce and print on the screen? Well, 3. I want it to say J-U-N for June. So I'll just type the number 3. right parenthesis, and then tap enter on the keyboard, and it worked.
I've successfully extracted information from the middle of this cell using MID and produced them and printed them on the screen here in column G. And then I can just click on the cell and then double click the autofill handle, and there we go. We've extracted the month from the middle of this data.
If I wanted to, I could easily then grab the year, By using the write function, I'll just click here on B2, comma, and counting from the right, if I want the year, I would need four characters. Tap enter on the keyboard, and then click on the cell, double click the autofill handle. So now I can easily identify the month and year that each of these people were added to the list. So I hope that you can see the power of using left, right, and mid functions in Excel.
They really can be very useful. Thanks for watching. I hope you found this tutorial to be helpful.
If you did, please like, follow, and subscribe. And when you do subscribe, click the bell next to the subscribe button. That way you'll be notified whenever I post another video.
If you'd like to support my channel, become a supporter of mine through my Patreon account, and you'll see a link to that in the description below.