Microsoft Office Tutorials and References
In Depth Information
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 2013 Operating Budget, the formula then returns Operating Budget.
This formula returns an error if the cell contains only one word. The following formula 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 follow-
ing formulas will not handle these complex cases. However, they still give you a signi-
ficant head start if you're willing to do a bit of manual editing to handle the special
cases.
Search JabSto ::




Custom Search