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




Custom Search