Microsoft Office Tutorials and References
In Depth Information
Using the substring text functions
Unexpected results can occur when the text contains an apostrophe, however. For example,
if cell A1 contains the text it wasn’t bad , the PROPER function converts it to It Wasn’T Bad .
Using the substring text functions
The following functions locate and return portions of a text string or assemble larger
strings from smaller ones: FIND, SEARCH, RIGHT, LEFT, MID, REPLACE, SUBSTITUTE, and
CONCATENATE.
The FIND and SEARCH functions
You use the FIND and SEARCH functions to locate the position of a substring within a
string. Both functions return the position in the string of the character you specify. (Excel
counts blank spaces and punctuation marks as characters.) These two functions work the
same way, except FIND is case sensitive and SEARCH allows wildcards. Both functions take
the same arguments: ( find_text, within_text, start_num ). The optional start_num argument
is helpful when within_text contains more than one occurrence of find_text . If you omit
start_num , Excel reports the first match it locates. For example, to locate the p in the string
A Night at the Opera , you type the formula =FIND("p", "A Night at the Opera") . The
formula returns 17 , because p is the 17th character in the string.
If you’re not sure of the character sequence you’re searching for, you can use the SEARCH
function and include wildcards in your find_text string. Suppose you used the names Smith
and Smyth in your worksheet. To determine whether either name is in cell A1, type the
formula =SEARCH("Sm?th", A1) . If cell A1 contains the text John Smith or John Smyth , the
SEARCH function returns the value 6 —the starting point of the string Sm?th .
If you’re not sure of the number of characters, use the * wildcard. For example, to find
the position of Allan or Alan within the text (if any) stored in cell A1, type the formula
=SEARCH("A*an", A1) (which would also return the word American ).
Note
You might notice the presence of several text functions that look similar to others but
end in “B,” such as FINDB, LEFTB, LENB, REPLACEB, and so on. These functions perform
similar tasks to their “non-B” counterparts, but they are included specifically for use
with double-byte character sets, such as Japanese, Chinese, and Korean.
Search JabSto ::




Custom Search