Microsoft Office Tutorials and References
In Depth Information
Extracting Words from a String
Extracting Words from a String
The formulas in this tip are useful for extracting words from text contained in a cell. For example,
you can use a formula to extract the first word in a sentence.
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 that:
=LEFT(A1,FIND(“ “,A1)-1)
This formula returns all the text before the first space in cell A1. However, the formula has a slight
problem: It returns an error if the text in cell A1 contains no space characters, because it consists
of a single word. A slightly more complex formula solves the problem by using the new IFERROR
function to display the entire cell contents if an error occurs:
=IFERROR(LEFT(A1,FIND(“ “,A1)-1),A1)
If you need the formula to be compatible with earlier versions of Excel, you can’t use IFERROR.
Therefore, use an IF function and an ISERR function to check for the error:
=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 only
from left to right. Therefore, the problem rests with locating the last space character. The
following formula, however, solves this problem. The formula 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,” “,””)))))
This formula, however, has the same problem as the first formula in the preceding section: It fails
if the string doesn’t contain at least one space character. The solution is to use the IFERROR
function and return the entire contents of cell A1 if an error occurs:
 
Search JabSto ::




Custom Search