Look for a character in a range
Posted by Travis on October 03, 2001 8:30 PM
Is there a way to look for a character within a range of cells?
For example if there is a "?" in A1:D10 then True else Flase. Where the values in A1:D10 can be text numbers or formulas.
Posted by Eric on October 04, 2001 3:41 AM
There is a post below that appears to deal with your issue (Link Inside)
32391.html
starts a thread that appears to be directly related to your problem
Posted by Travis on October 04, 2001 5:59 AM
Not really what I'm looking for...
I am aware of how this would work on a single cell, but I want to look in an entire range (maybe array is a better word) I think the formula I want is more like a VLOOKUP, except that it would look at parts of the cell not the entire value
Posted by Eric on October 04, 2001 6:33 AM
Check out IML's post below, looks like he has what you're after (NT)
Posted by Aladin Akyurek on October 04, 2001 7:26 AM
Travis --
A formula cannot peek into formulae to see if they contain a specific char. What follows will check a range a values and return a logical as result.
=SUMPRODUCT(ISNUMBER(SEARCH("~?",A1:D10))+0)>0
Note that this looks for a question mark which needs to be escaped. I'm not using FIND expressly because of its inconsistent behavior regarding the chars with reserved meanings. For ordinary chars, you don't need the tilde for the formula to work.
SEARCH is case-insensitive. If you need case-sensitive searching, use FIND instead for chars for non-reserved meanings.
If you want to target also the formulae that cells might contain, you should ask VBA programmers for code.
Aladin
=========
Posted by Travis on October 04, 2001 7:43 AM
Aladin, Eric, & IML- Thanks for the help...Works great
Posted by Travis on October 04, 2001 7:43 AM
Aladin, Eric, & IML- Thanks for the help...Works great
