# Function to find out if a search string is part of a cell in a range of cells?

This would be great.

edit retag close merge delete

Sort by » oldest newest most voted

Reading " in a range of cells" as " in at least one of the cells of a range". =(SUMPRODUCT(ISNUMBER(FIND(SearchString; Range)) > 0) (Case sensitive)

=ISNUMBER(MATCH(SearchString; Range; 0) if 'Enable regular expressions in formulae' is enabled. (Case insensitive).

more

I tried =ISNUMBER(MATCH(SearchString; Range; 0). Regular expressions in formula is enabled. The result when doing this C:\fakepath\isnumber match - calc function.png is FALSE. Why?

( 2017-05-06 07:43:26 +0200 )edit

I cannot analyse, of course, as long as I don't know the contents of H$1 and of F6:G6 as well. ( 2017-05-06 12:06:09 +0200 )edit Can you open the pic in the link? This would show colors to see the referenced cells. ( 2017-05-06 12:50:09 +0200 )edit Of course I see the references but not the contents. If there is a specific trick: I am not interested to much in learning every trick in the world. Simply post the contents of the three referenceed cells here. Use the tool for including code ('preformatted text'). ( 2017-05-06 13:42:50 +0200 )edit No trick, you see the contents in the pic. But here H1: Uda F6: Sudan, Afghanistan G6 is empty  ( 2017-05-06 16:17:08 +0200 )edit OK. I tried to analyse the image to that level, but -sorry!- I won't do it next time. The missing G6 had no solution for me: There isn't even an empty cell. I am a stubborn old man and want the information my help is depending on as clear as possible. I also take the trouble to put things as clear as I can. Failure may occur, however. ( 2017-05-06 23:10:50 +0200 )edit Dear Cosmo, you know a lot and give very good help. And you know also what is needed to give best help. So it is right that you insist. I understand this fully. Do I understand it right that it would be good to give to you simply all cells with all data, formula ...? And no pic in this case? What I forgot to mention is: H6: =ISNUMBER(MATCH(H$1, F6:G6, 0))

I see now that both formulas of your answer are working fine. I guess I made some mistake before. Thank you.

( 2017-05-07 11:44:12 +0200 )edit

I would need a formula which is case insensitive and if somehow possible withoud need to enable regular espressions in formulas in the options.

That is because I did not find an offline bookmark manager. No I try to create one which I also want to be able to share with others without having them to perform any special steps (like enablng regular expressions in formulas ...).

It should work in ods, xls and xlsx in all major OS if possible. Also in German languages installations.

( 2017-05-07 11:50:53 +0200 )edit

If I remember correctly I already told you in one of the "entangled" threads what you need to get FIND working case insensitive: Embed all the strins to compare in a call to UPPER().
E.g. =ISNUMBER(MATCH(UPPER(SearchString); UPPER(Range); 0). If an error occurs it may be necessary to enter the formula explicitly for evaluation in array-mode. AOO will need that e.g.
(Continued...)

( 2017-05-07 12:19:12 +0200 )edit

(...continued)
That any spreadsheet made with any software will also work after saving to an alien format under an alien software cannot be assured. MS Office e.g. is "pure commerce" and its raison d'etre still is to be incompatible and to find believers in its supremacy. (Variety of "white supremacy"?) MS succeed with this strategy to date.

Also: UPPER and LOWER do not fully invert mutually!

(Would you mind to explain the "Cosmo" above?)

( 2017-05-07 12:26:41 +0200 )edit