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(“

“,RIGHT(A3,LEN(A3)-SEARCH(“ “,A3)))).

✓
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:

RIGHT(A3,LEN(A3)-SEARCH(“ “,A3)).

✓
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.

3.Enterastringoftextthatyouwantinalargerstring,enclosedwith

doublequotationmarks,orclickacellthatcontainsthetext.

4. Enter a comma (
,).

5.Clickacellthatcontainsthelargertextorenteritsaddress.

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.