Microsoft Office Tutorials and References

In Depth Information

the following seven characters in cell A1: space, hyphen, colon, asterisk, underscore, left parenthesis, and right

parenthesis.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”

“,””),”-”,””),”:”,””),”*”,””),”_”,””),”(“,””),”)”,””)

Therefore, if cell A1 contains the string
Part-2A - Z(4M1)_A*,
the formula returns
Part2AZ4M1A.

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:

•
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 compar-

isons. Wildcard comparisons are not supported.

•
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
5,
the position of the first
m
(either upper-

case or lowercase):

=SEARCH(“m”,”Big Mamma Thornton”,1)

You can use the following wildcard characters within the first argument for the SEARCH function:

•
Question mark (?):
Matches any single character

•
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. If you want to find a tilde, type two tildes.

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)