Microsoft Office Tutorials and References

In Depth Information

=IF(ISERR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)),“Invalid

Column”,LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1))

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))

The preceding 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.

In some cases, the Flash Fill feature (introduced in Excel 2013) can substitute for creat-

ing formulas that extract text from cells. See Chapter 16 for more information.

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 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 works from left to right

only. 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,” “,””)))))