Ask Your Question

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

asked 2017-05-05 19:19:11 +0200

inJesus gravatar image

This would be great.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-05 22:47:59 +0200

Lupp gravatar image

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).

(Many variants! See also my answer to your question here.)

edit flag offensive delete link 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?

inJesus gravatar imageinJesus ( 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.

Lupp gravatar imageLupp ( 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.

inJesus gravatar imageinJesus ( 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').

Lupp gravatar imageLupp ( 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
inJesus gravatar imageinJesus ( 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.

Lupp gravatar imageLupp ( 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.

inJesus gravatar imageinJesus ( 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.

inJesus gravatar imageinJesus ( 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.

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

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?)

Lupp gravatar imageLupp ( 2017-05-07 12:26:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-05 19:19:11 +0200

Seen: 215 times

Last updated: May 05 '17