tells Excel what text to look within, to find the text you’re looking for. And the optional last argument, called Start Num, tells Excel where to start searching within the Within Text. If you don’t include the start num, Excel will begin at the first character in the Text Within which you’re asking it to search. So, let’s look at this: what we’re using the Search() Function for in this case, in this example worksheet, we’re looking for the location of the “,” (comma). The reason we would want to know that is, it tells us how many, it would allow us to calculate how many characters are in the last name, and how many characters are in the first name, because we know that a “, ” (comma space) separate the last name and the first name. So
our Search() Function here, the first argument is the “,” (comma) and the second argument is this Complete Name in cell A9, and our answer for the first one, as we can see is a 7. The “,” (Comma) is at position 7 after the last name of six characters. And in the second entry in the same table the comma location is at position 5 after a last name with a four character length. Now we’re going to look at another example using the Search() Function, on another worksheet. It’s called Sep Text Ex2. In this case we want to know how many spaces are in the name, because we want to be able to extract the Middle Name or Initial. So, in a complete name like the first one here, there is no Middle Name or Initial, so there’s only one space, the one after the “,” (comma), after the Last Name. But notice, in the second entry here, we’ve got two spaces. There’s a ” ” (space) after the “,” (comma) that follows the Last Name, and there’s also a ” ” (space) after the First Name, before the Middle Initial. So, if we’re going to try to extract Last Name, First Name, and Middle Name or Initial, we have to know the location of the first ” ” (space), and the location of the second ” ” (space).
So, in the case to find the first ” ” (space), we can still just give the Search() Function two arguments: the first argument is the ” ” (space) character that we’re looking for, the second argument is the text where we’re looking.
But, if we’re looking for a second ” ” (space), we have to give it the Start Num argument. In this case, the Start Num is going to be the location of the first ” ” (space) in C9,
plus 1, because we want to start looking for the second ” ” (space) AFTER the occurrence of the first ” ” (space). So we’re going to start in position 9 in the first row of this table.
And notice the result of that, is the VALUE error, because there is no second ” ” (space). However, in the other rows of this table there is a second ” ” (space). The second ” ” (space) is between the First Name and the Middle Initial. In the third entry, it’s between the First Name and the Middle Name.
And that is the end of this video. Come back for other text string videos to learn how we use these all together.