Microsoft Office Tutorials and References
In Depth Information
=IF(OR(LEFT(A1,2)={“Mr”,”Dr”,”Ms”}),RIGHT(A1,LEN(A1)-
(FIND(“.”,A1)+1)),A1)
Counting the number of words in a cell
The following formula returns the number of words in cell A1:
=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1),” “,””))+1
The formula uses the TRIM function to remove excess spaces. It then uses the SUBSTITUTE function to create
a new string (in memory) that has all the space characters removed. The length of this string is subtracted from
the length of the original (trimmed) string to get the number of spaces. This value is then incremented by 1 to
get the number of words.
Note that this formula returns 1 if the cell is empty. The following modification solves that problem:
=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),”
“,””))+1)
Excel has many functions that work with text, but you're likely to run into a situation in
which the appropriate function just doesn't exist. In such a case, you can often create
your own worksheet function using VBA. Chapter 26 also contains a number of custom
text functions written in VBA.
Search JabSto ::




Custom Search