Scenario: Separating First Name and Last Name
There are many ways to separate a lengthy text string in Excel…
… Especially in a situation where we’d like to separate a full name string into separate first and last name text strings.
Let’s see the value of the ‘FIND’ function combined with the ‘LEFT’, ‘RIGHT’, and ‘LEN’ functions. In case you’re not familiar with these functions, let’s take a quick look…
The ‘LEFT’ and ‘RIGHT’ functions return a substring of text based on the number of characters we specify from the left or right, respectively.
The syntax for both requires two arguments:
- ‘text’ which is either a literal string or a cell reference to a cell containing a string.
- ‘num_chars’ which is simply the number of characters from either the left or right.
Theoretically, the ‘num_chars’ argument is optional. But we will be using it in our scenarios.
So, the result of ‘=RIGHT(“Saturday”,3)’ would be ‘day’ while the result of ‘=LEFT(“Sunday”,3)’ would be ‘Sun’.
The ‘LEN’ function returns the length of a text string and its only parameter is ‘text’. Like with all the functions we are discussing, the ‘text’ argument can be a literal string or a cell reference of the cell that contains a string.
The result of ‘=LEN(“length”)’ would be ‘6’ since there are six characters or letters in the text string ‘length’.
A word of caution when using the ‘LEN’ function: beware of spaces.
Spaces are considered characters within a string. This is especially important to be aware of in our example. Here, we’re splitting full name strings into separate first and last name substrings. These contain spaces, that we should mindful of.
NOTE: Since our ‘find_text’ argument is the space between first and last name, our ‘FIND’ function is interchangeable with the ‘SEARCH’ function.
Either function will achieve the same outcome here.
We now need to set up our formula to return the position of the space between the first and last name.
This is simply ‘=FIND(“ “,A2)’ where ‘A2’ is the cell reference that contains ‘Ryan Thompson’.
Notice in column B, the result is 5. Since ‘Ryan’ is four letters, clearly this is the correct result.
Now we will get creative with that result by subtracting ‘1’ from it.
Notice that in column C we now have that result.
We do this with a simple formula that subtracts ‘1’ from the cell reference of our previous formula’s result, or ‘=B2-1’.
But, we could also combine this into our original formula like ‘=FIND(“ “,A1)-1’.
The next step is to extract the first name only from our original string… And that’s super easy!
Because you’ve just figured out how to get the number of characters prior to the space between the first and last name.
We can do this using the ‘LEFT’ function and the result of our previous formula where we subtracted ‘1’ from our ‘FIND’ function.
This will simply be ‘=LEFT(A2,4)’.
We could also write the formula replacing the ‘num_chars’ argument with the cell reference ‘C2’ since it contains the result we need.
See the figure below to review the ways in which we can build our formulas to extract the first and last names from our original string.
We will start by parsing out the first name from the original text in column A on row 2.
Row 3 shows the formula that we build for each step in the progression to our result in columns B through D.
For the first name, we progress from getting the position of the space between names using the ‘FIND’ function. This is shown in column B (‘FIND result’).
Our next step is to get the length of the first name by subtracting ‘1’ from the ‘FIND’ result.
This method of finding the length of the first name will always hold true since the ‘FIND’ function result is based on locating the position of the space after the first name.
That value is listed in column C (‘FIND result – 1’).
The final step in the progression to building our formula to extract the first name is shown in column D (‘First Name’).
We do this with the ‘LEFT’ function, using the results of our previous formula for the ‘num_chars’ argument.
So, as we can see, without the result of the ‘FIND’ function, the ‘LEFT’ function is not useful.
To extract the last name from our original string we now turn to the ‘LEN’ function. Here we simply use its result when applied to the original string and subtract the result of our original ‘FIND’ formula.
We know the number position of the space between names is ‘5’.
If we subtract that from the length of the entire string, we get the length of the last name.
Then all we need to do is insert this as our ‘num_chars’ argument in the ‘RIGHT’ function.
That’s it. (See cell F3 in the above figure to see how simple this is.)
The figure below shows this same process of building our formulas without using cell references in substitution of our ‘FIND’ and ‘LEN’ function on row 5.
By comparing to our formulas on row 3, we can see how easy it can be to build complex functions to get the job done.
The best method while learning is to simply break down the process in steps like we have done.
We can see how fundamental the results of ‘FIND’ can be to a larger and more complex formula to achieve solutions to problems. It is amazing to see how such a simple function can become so powerful when combined with others!