Can search see characters following ^~ in excel ?

[Guest]

My spreedsheet looks for unix printer aliases and matches them to numeric
code values. However excel cannot see past "^~" characters and hence does not
find a match eg "5CQ-MKPCTCC^~CC^~5CQ-CCCAMPBW" will only see
"5CQ-MKPCTCC^~". Can you tell me if there is a work around?

 

[Bearacade]

the Tilde is a special character in Excel..

So to do a search, put another Tilde in front of it..

5CQ-MKPCTCC^~~CC^~~5CQ-CCCAMPBW

This is also true with the * and ?

 

[Ron Rosenfeld]

On Wed, 5 Jul 2006 09:48:02 -0700, Geoff Thomas <Geoff

My spreedsheet looks for unix printer aliases and matches them to numeric
code values. However excel cannot see past "^~" characters and hence does not
find a match eg "5CQ-MKPCTCC^~CC^~5CQ-CCCAMPBW" will only see
"5CQ-MKPCTCC^~". Can you tell me if there is a work around?

There is no inherent reason for what you write "excel cannot see past "^~" "

Most likely, your SEARCH string is incorrect. Since the tilde is used to
qualify wild card characters, when you want to search for an actual tilde
followed by a non-wildcard character, you need to type a double tilde. eg:

=SEARCH("^~~5CQ",A1)


--ron

 

[Guest]

Thanks for your answer, I t has led me to another problem. Excel now does the
search, but comes up with the wrong data. How do I gt it to do an exact match
when searching and if not found return ith a N/A?