I’m going to jump straight to the Separating Text Example 2 worksheet.
In this example, we have a Complete Name that may or may not include a Middle Name or Initial. And we want to have formulas that can extract the Last Name, the First Name, and a Middle Name or Initial, if it is included in the Complete Name. In order to have ONE formula do that successfully for the First Name and the Middle Name or Initial, we have to have TWO DIFFERENT OUTCOMES possible in the formula in order to do that successfully, because if there IS a Middle Name available, we’ll have to calculate the length of the First Name DIFFERENTLY than if there ISN’T a Middle Name available.
The IF() Function used to extract the First name: we first test to see whether there’s an ERROR when we search for a second ” ” (space). If it’s TRUE that there’s an error, that means there IS NO Middle Name, because there IS NO second ” ” (space),
and we’re going to extract the First Name, STARTING at the first ” ” (space) location + 1, and then extract the number of characters calculated by the length of the Complete Name – (minus) the location of the first ” ” (space).
And then, we can confirm that that makes sense, by calculating 12 – (minus) 8 = (equals) 4. We see that is indeed the length of the First Name for this first entry in this table.
I’m gonna go on to the second entry, and in this case there IS a Middle Initial available, so we’re going to be using the Value_If_False in this IF() Function.
We’re still going to start our extraction of the First Name at the location of the first ” ” (space) + (plus) 1, but this time our length is going to be calculated by subtracting the location of the first ” ” (space) from the location of the second ” ” (space), and then subtracting an additional 1 from that. When we extract the Middle Name or Initial, we again have to have two different outcomes.
If there’s an ERROR, then we know that there IS NO Middle Name, so then we need an “” (empty) value in this column. If there is NOT an error, the Value_If_False formula will use the Right() Function to extract the correct number of characters from the right of the Complete Name string. And let’s take a look at the formula in a cell where we needed to do this. In the last entry of this table
we see that we’re extracting from the right of the Complete Name, starting from the right, the total length of the Complete Name that’s in B11, and subtracting from that the location of the second ” ” (space): 21 – (minus) 15 = (equals) 6. And that is the length of the Middle Name.
Thank you. Look for more IF() Function examples on our website, or on the YouTube channel.