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,” “,””)))))
Search JabSto ::




Custom Search