How to Efficiently Organize Data in Excel: Mastering LEFT, MID, and RIGHT Functions

Excel is a powerful tool for organizing, analyzing, and manipulating data. One common task in Excel is data extraction – pulling specific pieces of information from a larger dataset. This can be particularly useful when dealing with strings of text where you need to extract certain parts, such as a substring of a serial number, a specific date format, or a part of an address. Excel provides several functions to make this task easier, and among the most useful are the LEFT, MID, and RIGHT functions. In this blog, we’ll explore how to use these functions to efficiently extract data in Excel.

What Are LEFT, MID, and RIGHT Functions in Excel

Before diving into examples, let’s define what each function does:

– LEFT: The LEFT function in Excel is used to extract a specified number of characters from the start (left side) of a text string.
– MID: The MID function is used to extract a specific number of characters from any part of a text string, starting at the position you specify.
– RIGHT: The RIGHT function extracts a specified number of characters from the end (right side) of a text string.

Understanding Syntax:

– LEFT(text, [num_chars])
– MID(text, start_num, num_chars)
– RIGHT(text, [num_chars])

Where `text` is the string from which you want to extract characters, `start_num` is the position of the first character you want to extract, and `num_chars` is the number of characters to extract.

Step-by-Step Examples

Example 1: Extracting Area Codes from Phone Numbers

Imagine you have a list of phone numbers in the format (123) 456-7890, and you want to extract the area code.

| Original Phone Number | Extracted Area Code |
|———————–|———————|
| (123) 456-7890 | 123 |
| (456) 789-0123 | 456 |
| (789) 012-3456 | 789 |

To extract the area code, you can use the MID function since the area code starts after the first character and is three characters long.

`=MID(A2, 2, 3)`

Example 2: Extracting Domain Names from Email Addresses

Example 3: Extracting First Names from Full Names

Conclusion

The LEFT, MID, and RIGHT functions are incredibly useful for extracting specific data from text strings in Excel. By understanding how to use these functions, you can save time and avoid manual data entry errors. Whether you’re dealing with large datasets or just need to reformat some information, mastering these functions will enhance your Excel skills and help you organize your data more efficiently. Remember to practice with different datasets to become more comfortable with these functions and discover the various ways they can be applied to your data extraction needs.

Live Excel Support Transcript Example:

Below is a live transcript of one of our Excel experts helping a customer to organize excel data:

Full Conversation:

Customer: Can you explain to me?
Technician: Yeah, let me, I can show you pretty easily so you can have a better understanding.
Customer: So right now, when I extrapolate data from Shopify, I’ve set up SKU numbers. The SKU number has the style number, the size. And when it comes out, I can obviously use a pivot table to tell, and I can, last time I called, the guy showed me how to set it up so I could separate the size. Everything’s integrated into it, but I wanna work smarter, not harder, because my business is growing like crazy. So I gotta get this thing set up so I can pull the data out more readily, and so I can lay it out in a format that I’m used to looking at. So can I just show those to you so you can see what I’m looking at here?
Technician: Yeah, you can show your screen.
Customer: All right, perfect. Did you speak to someone else before, too?
Technician: Oh, yeah, I spoke to somebody once before, and they helped me figure out how to do like a data separation in my, yes.
Customer: So, share screen.
Technician: All right, let me know when you’re able to share your screen.
Customer: Oh, man, I just upgraded my friggin’ Mac, man, and so everything has changed now. It’s like crazy. I’m like, oh my God, browser. All right, so how are we doing? Can you see my screen now?
Technician: No. Let’s try it again. Share screen, window. Let’s go here and share.
Customer: You’re not being billed yet.
Technician: No, that’s not it. Meeting, why is it not doing that? It’s in the Geekr screen. It’s in the Geekr screen, so.
Customer: Yeah, you know like.
Technician: All right, so let me just pick the right one. So it’s saying Chrome tab. It’s not a tab, it’s a window.
Customer: Yeah. One of the tabs in your Chrome browser says Geekr on it.
Technician: Yeah. Let’s do that. Let’s try this. There we go. There, I see it now. Now it worked, right?
Customer: Yeah.
Technician: Okay, so, but now I lost it. Where the hell did it go?
Customer: All right, so the data comes out of, oh, there. Shit, now I’m embarrassed to say I can’t see it on my own desktop here. Probably sales right there.
Technician: What’s that? I see sales.
Customer: You can look on the sales.
Technician: Yeah, yeah. Now I’m looking for the file Geekr stop sharing. This is really embarrassing, Todd.
Customer: Yeah, take your time.
Technician: Sales. Okay, there we go. Now I’m back. All right. So you can see my screen, right?
Customer: Yeah, I do.
Technician: All right, so the data comes out of Shopify looking like this, right? So here’s the product title tells me that it’s a eight inch mock toe boot. So there are four variants. There’s eight inch mock toe boot. There’s eight inch steel toe, steel mock toe boot. And then there’s six inch mock toe and six inch steel mock toe. So the variant skew is identified by the first number is the style number. So it’s one. Second number tells you the height. The second, the third and fourth number tell you the color. So it’d be great to be able to separate those out differently. So I’m wondering if I, from Excel, from an Excel purpose, should I set this data points up? So the style numbers have a space. So they have a dash. So they have something in there. So when I pull them out and they come out looking like this, right? So this goes 10, let it, it sets this for style. So let’s say the way that, the way this would normally look here, let me see if I can. So you understand A4, you know, you know what each digit means already, right?
Customer: Yes, I know what each.
Technician: You wanna separate it out?
Customer: Yes.
Technician: You can extract, let’s see here. Let me just give you the command. I mean, I think you can assign dimensions as well. Here’s an A4, you know.
Customer: Hm. That just looks different. So let me just add in the standard. Let’s see, here an A4. And over here, lets just select them, and let it look like this. There you go. Let’s go ahead and do a double click on this, and now I’m gonna select what they are.
Technician: Yep.
Customer: Equal, there you go.
Technician: Yep.
Customer: Equals.
Technician: Yep.
Customer: Left.
Technician: Left.
Customer: Then parentheses.
Technician: Yep.
Customer: Then select A4.
Technician: A4? Yes.
Customer: Yeah, comma. And one. Close. Enter. So that gives you the, and then put a title on C3. What does that one mean?
Technician: Spell number.
Customer: Okay. And do the same thing for D and E, whatever characters you know.
Technician: Okay. No, no, but you have to change that. You have to change that, right?
Customer: Yeah.
Technician: So just be column, it would be B4. So it’d be A4, but number two, right?
Customer: All right. So that’s a fix, right? A4, but two, you know, you need to change the character.
Technician: Yeah, so just be column, it would be B4. So it’d be A4, but number two, right?
Customer: All right. So that’s a fix, right? A4, but two, you know, you need to change the character.

Technician: Yeah, so just be column, it would be B4. So it’d be A4, but number two, right?
Customer: All right. So that’s a fix, right? A4, but two, you know, you need to change the character.
Technician: Yeah, so just be column, it would be B4. So it’d be A4, but number two, right?
Customer: All right, that’s magic. And so then the last one would be from the right. So that would be equals right. You need the M, right? Or something like that?
Technician: I actually need, yeah. So that M, that tells me it’s a medium. That’s a different width. Like a 10 and a half M is different than a 10 and a half wide. So 10, five M is different than 10, five wide. So yes.
Customer: Yeah. And that should be, that technically in this order, I would want that to be the last column. So I’d want to probably move that here.
Technician: Yeah.
Customer: So I would do equals right. All right. And then I want that to be a four. A four, yeah. And then I want that just to be the first. Yeah, comma one. Comma one. Beautiful, all right. And now here’s where it’s a little tricky. Is that some of these are gonna be three digits and some of these are gonna be two digits. So for instance, like the one below it, the size is 10 and the one above it, the size is 10.5 or 105. Do you mind if you get an M on there? Do I mind if I get an M on there? I guess it wouldn’t be, I’ve already got it repeated there. So if it said 10.5 M there, it would just be doubling up on it. So I don’t think so. I think you’re gonna find that my next challenge of what I wanna do, you can probably give me some advice on too from a sorting perspective, because I’m gonna show you another challenge that I have when it lays out. So anyway, so. Let’s do that then. Copy the mid, the 19 formula. Copy it over. Equals mid. Oh, sorry, was that a mid we just did? That was at 19 we did mid, so just copy it, okay. Yeah, let’s copy that, yeah. Okay. It went to B4. It’s supposed to be A4, so change that to A4. Okay. Yep. Then how many over to the right? See, that’s gonna vary. Oh, okay, see. It’s gonna vary from each one, yeah. The right is gonna be from that. Sometimes it’s three digits, sometimes it’s two, so. Let me just double check exactly what we need to do. Okay. So I want the third and the fourth digit in that style number. Let me pause the call until I figure out a formula. Okay. Okay, how about equals mid? Equals mid. That’s parentheses. Yep. And then A4. A4. Then which one do you want again? Comma? So I want the third and the fourth. All right, do three. So do comma and then do three or do? Comma three. Comma three. Put dash. Dash four. Close. One more, you need another comma. Okay. Comma and then two. Two. Two characters. Two? Yeah. Okay. Close it. I don’t know if it’s gonna work now. Let’s go back to that. Remove that two on the end probably. Okay. Actually, remove the dash four. Okay. Yeah, put comma two. Comma two. Enter. There you go. Okay. All right, that’s magic. And so then the last one would be from the right. So that would be equals right. You need the M, right? Or something like that? I actually need, yeah. So that M, that tells me it’s a medium. That’s a different width. Like a 10 and a half M is different than a 10 and a half wide. So 10, five M is different than 10, five wide. So yes. Yeah. And that should be, that technically in this order, I would want that to be the last column. So I’d want to probably move that here. Yeah. So I would do equals right. All right. And then I want that to be a four. A four, yeah. And then I want that just to be the first. Yeah, comma one. Comma one. Beautiful, all right. And now here’s where it’s a little tricky. Is that some of these are gonna be three digits and some of these are gonna be two digits. So for instance, like the one below it, the size is 10 and the one above it, the size is 10.5 or 105. Do you mind if you get an M on there? Do I mind if I get an M on there? I guess it wouldn’t be, I’ve already got it repeated there. So if it said 10.5 M there, it would just be doubling up on it. So I don’t think so. I think you’re gonna find that my next challenge of what I wanna do, you can probably give me some advice on too from a sorting perspective, because I’m gonna show you another challenge that I have when it lays out. So anyway, so. Let’s do that then. Copy the mid, the 19 formula. Copy it over. Equals mid. Oh, sorry, was that a mid we just did? That was at 19 we did mid, so just copy it, okay. Yeah, let’s copy that, yeah. Okay. It went to B4. It’s supposed to be A4, so change that to A4. Okay. Yep. Then how many over to the right? See, that’s gonna vary. Oh, okay, see. It’s gonna vary from each one, yeah. The right is gonna be from that. Sometimes it’s three digits, sometimes it’s two, so.