Microsoft Office Tutorials and References

In Depth Information

**Advanced Text Formulas**

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:\files\excel\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

the text following the last backslash character. If cell A1 doesn’t contain a backslash character,

the formula 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 of 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 simple modification solves

the problem by using an IFERROR function to check for the error:

=IFERROR(LEFT(A1,FIND(“ “,A1)-1),A1)

For compatibility with versions prior to Excel 2007, 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 rests with locating the
last
space character. The formula

that follows, however, solves this problem. It returns the last word of a string (all the text

following the last space character):

=RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)))))