Part II: Working with Formulas and Functions
244
For example, if cell A1 contains the text Blonde On Blonde and B1 contains the text Blonde,
the formula returns 2.The comparison is case sensitive, so if B1 contains the text blonde, the formula returns 0. The
following formula is a modified version that performs a case-insensitive comparison by converting
the characters to uppercase:=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))/LEN(B1)Extracting a filename from a path specification .......................................................
The following formula returns the filename from a full path specification. For example, if cell A1
contains c:\windows\important\myfile.xlsx, the formula returns myfile.xlsx.=MID(A1,FIND(“*”,SUBSTITUTE(A1,”\”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,
””))))+1,LEN(A1))This formula assumes that the system path separator is a backslash (\). It essentially returns all text
that follows the last backslash character. If cell A1 doesn’t contain a backslash character, the for-
mula returns an error.Extracting the first word of a string .........................................................................
To extract the first word of a string, a formula must locate the position of the first space character
and then use this information as an argument for the LEFT function. The following formula does
just that:=LEFT(A1,FIND(“ “,A1)-1)This formula returns all the text prior to the first space in cell A1. However, the formula has a
slight problem: It returns an error if cell A1 consists of a single word. A slightly more complex
formula that checks for the error using the IFERROR function solves that problem:=IFERROR(LEFT(A1,FIND(“ “,A1)-1),A1)Caution
The preceding formula uses the IFERROR function, which was introduced in Excel 2007. If your workbook will
be used with previous versions of Excel, use this formula:
=IF(ISERR(FIND(“ “,A1)),A1,LEFT(A1,FIND(“ “,A1)-1))Extracting the last word of a string ..........................................................................
Extracting the last word of a string is more complicated because the FIND function only works
from left to right. Therefore the problem is locating the last space character. The formula that fol-
lows, however, solves this problem by returning the last word of a string (all text following the last
space character):