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)
Search JabSto ::




Custom Search