Now, take a look at the Contact Names.
We can use LEFT to extract the Title used for each person.
However, since “Mr.”/”Ms.” and “Mrs.” have different string lengths, we have to input num_chars dynamically.
If you’ll notice, the period symbol “.” is common to all titles.
You can use this as the delimiter which indicates the string length needed.
It’s now just a matter of finding that delimiter in a given text! 🔎
The FIND function returns the position of a specified text within another text string.
1. The first Title should appear in cell F2. So, type “=FIND(“.
2. The first input in FIND is find_text. This is the character or delimiter that you are looking for.
Since we are looking for the period symbol, type:
=FIND(“.”,
3. Next input is within_text and this is the source string.
In this case, it is cell A2. So, the above formula becomes:
=FIND(“.”, A2
The third input is start_num. This is the starting position where Excel will look within the source string.
This input is optional and by default, FIND scans a string from the very beginning at the left.
5. Now, use the above FIND function as the second input in the LEFT function.
Your formula becomes:
=LEFT(A2,FIND(“.”, A2))
6. Hit Enter and fill in the other rows.
Result:
The SEARCH function can also be used similarly. But it is not case-sensitive, unlike the FIND function.
Also, you can use any symbol in the FIND function as we’ll see in the next example.
Next, you want to extract the First Name.
This is not as straightforward as the above examples. To do this, you first have to understand the concepts below.
Let’s get to it!
You know that the First Name is between the first and second spaces ” ” which are in positions 4 and 10 respectively.
To get the First Name, you can use MID to extract characters starting from position 5 (character immediately after first space) up to position 9 (character preceding the second space).
But how exactly do you type the formula?
1. Getting the starting position for the MID function is easy enough.
You use FIND just as in the previous example. But instead of the period symbol, use the space character ” “.
Also, add 1 to the result. The starting position formula becomes:
=FIND(” “,A2)+1
Try this in cell G2.
2. To get the position of the second space, you need to use the third input of the FIND function which is start_num.
You know that the position after the first space can be given by:
=FIND(” “,A2)+1
You can then use the above formula as the starting position of the second FIND function to find the second space like below:
=FIND(” “,A2, FIND(” “,A2)+1)
Try it out in cell G2.
3. Now, you can combine the above formulas to express the string length input in the final MID function in cell G2.
Your MID formula is basically:
=MID(A2, position AFTER 1st space, position of 2nd space – position of 1st space – 1 )
So, the final formula in cell G2 will be:
=MID(A2,FIND(” “,A2)+1,FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)
You deduct 1 at the end to remove the extra space from the result.
4. Fill in the rows below and watch Excel magic happen!
Incredible, isn’t it? 🤩
There’s one more interesting example below!
Finally, if you want to extract the Last Name, you use the RIGHT function.
Going back to the character count, you know that the Last Name is just the characters from the right of the Contact Name up to the second space.
This length can be expressed as the length of the entire text minus the position of the second space.
You already know how to get the position of the second space from the previous example.
So, how do you get the length of the whole text string?
You can use the LEN function to get the length of any input string.
1. In cell H2, type the formula below:
=LEN(A2)
2. Now, you can combine this with the formula for the second space to get the length of the Last Name:
=LEN(A2) – FIND(” “,A2, FIND(” “,A2)+1)
3. Finally, complete the RIGHT function by using the formula above as the number of characters to be extracted:
=RIGHT(A2,LEN(A2) – FIND(” “,A2, FIND(” “,A2)+1))
4. Hit Enter and fill in the rows below.
Result:
Congratulations! 👏
You have completed the practice workbook!