In this worksheet, we see that the Mid() Function is defined in column F. It has three arguments. The text you want to extract a text string from the middle of, the starting character position where you want to begin the extraction, and the number of characters that you want to extract. Now let’s take a look at the situation here. We have a Complete Name column, and we’re going to use the Mid() Function to extract the First Name from this Complete Name.
If there is no Middle Name, then, we could just use the Right() function. But if there is a Middle Name or a Middle Initial, then we want to extract the First Name from in between the first ” ” (space) and the second ” ” (space).
So we’re going to have two different outcomes. We’re going to use the Mid() Function for both of these outcomes, but we’re going to be calculating the length of the characters, the Number of Characters argument, differently, depending on whether there IS a value for the second ” ” (space) character position OR we have an error for that. So we’re going to use the IF() Function. The IF() Function is going to test for an error in the second ” ” (space) location. And if it’s TRUE that there’s an error, the Mid() Function will extract from the Complete Name cell starting at C9 + 1, the first ” ” (space) location + 1, and the Number of Characters will be the length of the Complete Name – (minus) the location of this first ” ” (space).
And you can see in this case: 12 – (minus) 8 would be 4, and that is the length of the First Name. However,
if there IS a second ” ” (space) value, that means we have to pull that First Name out from between the Last Name and the Middle Name. We’re going to have the same Text argument in column A. We’re going to start in the same location, the first ” ” (space) + 1. But, this time the Number of Characters calculated will be
the location of the second ” ” (space) – (minus) the location of the first ” ” (space) – (minus) 1. And we can take a look at that in the context of the third
example here. We’ve got
the Number of Characters calculated as: 15 – (minus) 9 would be 6, – (minus) 1 would be 5. Which is indeed the length of the First Name in this third entry.
And come back for more text videos. There’s a Separating Text Challenges that you might like to try out. Thank You.