Microsoft Office Tutorials and References
In Depth Information
Comparing, Finding, and Measuring Text
Admittedly, it’s a doozy. But it gets the job done. Here is an overview of what
this formula does:
✓ It’s an IF function and therefore tests for either true or false.
✓ The test is if an error is returned from SEARCH for trying to
find a space to the right of the first space: ISERROR(SEARCH(“
✓ If the test is true, then there is no other space. This means there is no
middle initial, so just return the portion of the name after the first space:
✓ If the test is false, then there is a second space, and the task is to return
the portion of the string after the second space. SEARCH tells both the
position of the first space and the second space. This is done by
nesting one SEARCH inside the other. The inner SEARCH provides the third
argument — where to start looking from. A 1 is added so the outer
SEARCH starts looking for a space one position after the first space:
RIGHT(A3,LEN(A3)-SEARCH(“ “,A3,SEARCH(“ “,A3)+1)).
Your eyes have probably glazed over, but that’s it!
The monster formula isolates last names from full names that include a
middle initial. A task for you to try, if you have any working brain cells left, is
to write a formula that isolates the middle initial, if there is one. Here’s how
to use FIND or SEARCH:
1. Position the cursor in the cell where you want the results to appear.
2. Enter either =FIND( or =SEARCH( to begin the function entry.
4. Enter a comma ( ,).
If you want the function to begin searching at the start of the larger
string, then go to Step 7. If you want to have the function begin the
search in the larger string at a position other than 1, go to Step 6.
6. Enter a comma ( ,) and the position number.
7. Type a ), and press Enter.