Part II: Working with Formulas and Functions
242
The following formula uses the REPLACE function to replace one character beginning at position 5
with nothing. In other words, it removes the fifth character (a hyphen) and returns Part544.=REPLACE(“Part-544”,5,1,””)Finding and searching within a string .....................................................................
The FIND and SEARCH functions enable you to locate the starting position of a particular sub-
string within a string:l 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.l (^) 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 Mama Thornton”,1)
The formula that follows, which uses the SEARCH function, returns 5 , the position of the first m
(either uppercase or lowercase):
=SEARCH(“m”,”Big Mama Thornton”,1)
You can use the following wildcard characters within the first argument for the SEARCH function:
l (^) Question mark (?) matches any single character.
l Asterisk (*) matches any sequence of characters.
Tip
If you want to find an actual question mark or asterisk character, type a tilde (~) before the question mark or
asterisk. n
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 replace part of a
text string with another string. In effect, you use the SEARCH function to find the starting location
used by the REPLACE function.