Microsoft Excel 2021: Advanced Formulas and Functions

Name: Microsoft Excel 2021: Advanced Formulas and Functions
Author: Bob Akbar

Learn how to manipulate text strings using advanced functions in Excel.

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Akbar and in these lessons you will learn how to manipulate text strings. In this lesson, I will show you how to use the right, left, and the mid. Let's take a look at our hours here. On our number, our number two, we have LG-MFG-WASH and the production time is 3.5 hours. And we're going to consider the waste, the decimal of an hour because an employee has to be paid for the full hour, but they're not producing the full amount. We're going to consider the pay hour, the hour that is fully used. The size is LG under the item code, under A2, that means it's a large, and the second item says item two, it's a washer, that's what the WSH is. And the provider is the manufacturer, MFG. So how do we break this information down? If you take a look at it for the waste, we have the right formula, and we have taken the right formula for B2, the number of hours, and taken the two off of it to achieve the results. So let's see how that number works. So let's try this on C5. We're going to hit equals, right, where the hours are is B5, and we want to, remember, this is doing this as a string. So 3.2 is considered three characters in text. We are not using this as a number function. So if we want the decimal included, we have to count it in as the two places. So we're going to go to from the right, which is two, and the decimal point. So we'll put two in there, and there we are. Now for the next one, for the pay hour, if you notice, we have used the left function. We've taken the same thing, the B column hours, and we've decided to, we only needed the one, number on the left side of the text. So that's what we've provided. And if we click at the very end, if you notice something funny happened, it automatically filled in all the values that I wanted. Let's do that one one more time. Remember, we can use this, we can drag this down, or if we click in the formula bar for the first one, and we simply click on the last one, there it is. They all fill in. Okay, and as you can see, you put the left B5 comma one. All right, let's do this one manually just to make sure that we know what we are doing. So let's try it for number four, and we're going to calculate the pay hours by using the left, and we simply want one character from the left of cell B4. And you notice, as always, Excel helps us, it's telling us what it wants us to put into the formula so that it can produce our results. So the text is before, and if you notice, it is highlighted, so we know that we're on the right cell, and we simply have to put the comma one character, that's all we want, and we can press enter. And there it is, there's our pay hours. We have done a similar thing for the left, and this time we've counted two on a two to get the LG part out. So let's try the same thing on the fourth line. So we do want to trim from the left. That's the function, because that's where the MD, the two letter designation for the sizes, and our item is located in a four. And then it asks us for the number of characters. And that's pretty simple. We want one, two, that's all we want, two characters, and there it is. Now for the next one, I'm going to click into a five as we do this, and I'm going to show you what it does, it kind of jumps to the heading for the table, and I don't want you to freak out when you see this. So we're going to go through our formula the same way that we did. Let's watch for the fact that it does a funny thing on the display. It doesn't affect the results, it just gives us the name of the column header. So we're going to do left, and I'm going to click on a five instead of typing get it. And this is what I'm talking about, if you notice, it says at item. So it's given us a reference of the item on this row. Do not freak out by this, it's absolutely fine. It's simply the way that I set up the table by defining the header. So we're still just going to put comma and the two characters that we need. And there we are. All right, wonderful. Now you may ask, Bob, this is great. I can take things from the right and things from the left. What about the middle? As you notice, that's where we have our provider. Our provider is in the middle. So the right or the left function are not going to help us with that, because no matter what we do, we're still going to end up with extra characters. Worry not, we have an example here. And if you notice, the mid function gives us the ability to define how much we're going to trim from the right and the left to get the middle. And we did that for G2 by doing the mid, A2, which is the item, LG dash MFG dash WASH. And we decided that we were going to trim this, you see it says, where do we start at and how many characters? So we want to start at the fourth character. And we want to take three characters from there. So L is one, G is two, dash is three, M is the fourth, where we want to start, and then we want to take the next three from where M is. So we are going to do MFG, that's our three characters. Okay, let's do one together. So we're going to do mid, and the text that we want is A4, and the start number. So we don't want the M, we do not want the D, and we do not want the dash, but we want the second M, which is the fourth character. So we're going to put four, and then the manufacturer in our item number is a three letter code. So we want the next three letters, MFG, and we're going to press enter, and there it is, beautifully transferred over. Thanks for watching.

Advanced Provider : Bob Akbar 3 Lessons · 17m in English

Course Description

In these lessons, you’ll learn how to isolate and transfer different parts of a string to uncover new information for your data analytics. There are times when a string—such as a product part number—will provide multiple fields of information. We’ll cover how to use the Right, Left, and Mid functions to separate out the sections of data needed for your analysis.

Then you’ll learn how to use the Concatenate function to assemble complex strings of data that include multiple identifiers, like when three different portions of a part number refer to size, model, and color, respectively. You’ll also find out how to use Concatenate in reverse to assemble the full part number from its individual identifiers.

What You'll Learn

Apply the Right, Left, and Mid functions to isolate and separate sections of data from a text string
Describe how the Right, Left, and Mid function parameters are defined
Use the Concatenate function to assemble complex strings of data that include multiple identifiers
Reassemble a full identifier from its individual parts using Concatenate in reverse
Recognize when to use trim versus assembly functions and text strings
Understand index cell reference

Key Takeaways

A single string, such as a product part number, can hold multiple fields of information that can be isolated for data analytics.
The Right, Left, and Mid functions are used to separate out the specific sections of data needed for analysis.
The Concatenate function assembles complex strings made up of multiple identifiers, such as size, model, and color portions of a part number.
Concatenate can be used in reverse to build a full part number from its individual identifiers.
Choosing between trim and assembly functions depends on whether you need to separate or combine text strings.

Frequently Asked Questions

What does this course cover?

It covers how to isolate and transfer different parts of a string using the Right, Left, and Mid functions, and how to assemble complex strings of data using the Concatenate function, including using Concatenate in reverse to rebuild a full part number from its individual identifiers.

What skills will I gain from this course?

You will gain skills in Microsoft Excel, spreadsheets, advanced function presentation, advanced function presentation data stream, data manipulation, and Excel formulas.

What lessons are included in this course?

The course includes lessons on Left, Mid, Right; Concatenate; and Index.

How are the Right, Left, and Mid functions used in this course?

They are used to separate out the sections of data needed for analysis, such as breaking a product part number into its component fields.

What is an example of using the Concatenate function in this course?

The course shows how to assemble a full part number when three different portions of the part number refer to size, model, and color respectively.