Microsoft Office Tutorials and References
In Depth Information
Text Functions
Finding and searching within a string
The Excel FIND and SEARCH functions enable you to locate the starting position of a particular
substring within a string:
h FIND: Finds a substring within another text string and returns the starting position of the
substring. You can specify the character position at which to begin searching. Use this
function for case-sensitive text comparisons. Wildcard comparisons are not supported.
h SEARCH: Finds a substring within another text string and returns the starting position of
the substring. You can specify the character position at which to begin searching. Use
this function for non–case-sensitive text or when you need to use wildcard characters.
The following formula uses the FIND function and returns 7, the position of the first m in the
string. Notice that this formula is case sensitive.
=FIND(“m”,”Big Mamma Thornton”,1)
The formula that follows, which uses the SEARCH function, returns , the position of the first m 5
(either uppercase or lowercase):
=SEARCH(“m”,”Big Mamma Thornton”,1)
You can use the following wildcard characters within the first argument for the SEARCH function:
h Question mark (?): Matches any single character
h Asterisk (*): Matches any sequence of characters
If you want to find an actual question mark or asterisk character, type a tilde (~) before
the question mark or asterisk.
The next formula examines the text in cell A1 and returns the position of the first three-character
sequence that has a hyphen in the middle of it. In other words, it looks for any character followed
by a hyphen and any other character. If cell A1 contains the text Part-A90, the formula returns 4.
=SEARCH(“?-?”,A1,1)
Searching and replacing within a string
You can use the REPLACE function in conjunction with the SEARCH function to create a new
string that replaces part of the original text string with another string. In effect, you use the
SEARCH function to find the starting location used by the REPLACE function.
 
Search JabSto ::




Custom Search