Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How/Can I do this Formula in Excel 2007?

Status
Not open for further replies.

BKearan

IS-IT--Management
Sep 27, 2007
45
US
Trying to compare part of a cell to a column of text.

Code:
=IF(ISNUMBER(SEARCH(Sheet1!B159:B192,E3)),"Yes","No")

The above does not work, but I think it gets the intent across. If I replace "Sheet1!B159:B192" with "jim" and E3 contains "Jack Jimbo" I would get a "Yes". I don't want to limit the text to just "Jim" I want to compare each cell in b159 thru b192 ( Which, lets say comma separates cells, may be : "Jack,Jim,Bo,Richard,Chris" )

Does that make sense? If so, Can it be done in Excel 2007? And if so, How?
 





Why are you using ISNUMBER if you're searching for TEXT?

Is the cell Value Jack or is that PART of the cell value?

Check out the MATCH function.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
From the Excel Help:
Code:
Check if part of a cell matches specific text
To do this task, use the IF, SEARCH and ISNUMBER functions.

 Note   The SEARCH function is case-insensitive. 
   
Formula Description (Result) 
=IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") Checks to see if A2 contain the letter v (OK) 
=ISNUMBER(SEARCH("BD",A3)) Checks to see if A3 contains BD (TRUE)

In short, I'm using "ISNUMBER" because that is what Microsoft said to do. (and I don't know any better)

Your second question didn't make much sense.
I'll try to clarify my question :
The value of cell E3 is "Jack Jimbo"
If I use =IF(ISNUMBER(SEARCH("Jim",E3)),"Yes","No") as the formula, I get a Yes - which is good.
I do not want to compare cell E3 to "Jim", I want to compare cell E3 to a whole Range of cells - Sheet1!B159:B192 - to see if any of those cells match a part of cell E3's value.
b159 = "timmy"
b160 = "Joe Jim"
b161 = "buck"
b162 = "Larry"

I want the formula to tell me that the Value of E3 ("Jim") was found in Sheet1!B159:B192 (a part of b160 is "Jim")
or the reverse, one of the cells in the range contains a part of the value in E3.
 
I want the formula to tell me that the Value of E3 ("Jim") was found in Sheet1!B159:B192 (a part of b160 is "Jim")
or the reverse, one of the cells in the range contains a part of the value in E3.

Doah! That should be :

I want the formula to tell me that a cell in Sheet1!B159:B192 contains a part of the Value of E3 (If a part of b160 is "Jim", or the value of b168 is "Jim", the result is "YES")

I would suppose that ISNUMBER is used because search returns the number of the character the string is found in. So if it returns a number, then the string was found, but, if it returns "null" the string was not found.

I just want to know if it is possible to put a Range of cells to check instead of just one static string of text.
 



The SEARCH function...
HELP said:
SEARCH, SEARCHB

SEARCH and SEARCHB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
...
SEARCH(find_text,within_text,start_num)

You might try using Find within your range from the Edit menu.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
This works:
Code:
=OR(ISNUMBER(SEARCH(Sheet1!B159:B192,E6)))
IF you set the formula to an "array" formula by selecting the cell, press "F2" then press "ctrl+shift+enter"

It tells me that, yes, at least one cell in b159 thru b192 had a value that was somewhere in the value of cell E6.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top