Microsoft Office Tutorials and References
In Depth Information
Advanced Text Formulas
This formula, however, has the same problem as the first formula in the preceding section: It fails
if the string does not contain at least one space character. The following modified formula uses
the IFERROR function to avoid the error value:
=IFERROR(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE
(A1,” “,””))))),A1)
For compatibility with versions prior to Excel 2007, use this formula:
=IF(ISERR(FIND(“ “,A1)),A1,RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-
LEN(SUBSTITUTE(A1,” “,””))))))
Extracting all but the first word of a string
The following formula returns the contents of cell A1, except for the first word:
=RIGHT(A1,LEN(A1)-FIND(“ “,A1,1))
If cell A1 contains 2010 Operating Budget, the formula then returns Operating Budget.
This formula returns an error if the cell contains only one word. The formula below solves this
problem and returns an empty string if the cell does not contain multiple words:
=IFERROR(RIGHT(A1,LEN(A1)-FIND(“ “,A1,1)),””)
For compatibility with versions prior to Excel 2007, use this formula:
=IF(ISERR(FIND(“ “,A1)),””,RIGHT(A1,LEN(A1)-FIND(“ “,A1,1)))
Extracting first names, middle names, and last names
Suppose you have a list consisting of people’s names in a single column. You have to separate
these names into three columns: one for the first name, one for the middle name or initial, and
one for the last name. This task is more complicated than you may initially think because not
every name in the column has a middle name or middle initial. However, you can still do it.
The task becomes a lot more complicated if the list contains names with titles (such as
Mrs. or Dr.) or names followed by additional details (such as Jr. or III). In fact, the
following formulas will not handle these complex cases. However, they still give you a
significant head start if you’re willing to do a bit of manual editing to handle the special
cases.
 
Search JabSto ::




Custom Search