This would be great.
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.)
I tried =ISNUMBER(MATCH(SearchString; Range; 0). Regular expressions in formula is enabled.
The result when doing this
isnumber match - calc function.png
is FALSE. Why?
I cannot analyse, of course, as long as I don’t know the contents of H$1 and of F6:G6 as well.
Can you open the pic in the link? This would show colors to see the referenced cells.
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’).
No trick, you see the contents in the pic.
But here
H1: Uda
F6: Sudan, Afghanistan
G6 is emptyOK. 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.
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.
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.
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…)
(…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?)
Dear Lupp
I am sorry. I mixed up names because Cosmo is helping me in the linuxmint-forum a lot. Please forgive me.
=(SUMPRODUCT(ISNUMBER(FIND(UPPER(SearchString), UPPER(Range))) > 0))
Great help! Thank you!
Btw, I clicked the code-symbol to insert the formula but it destroys the text and does not work properly here.
(I edited the comment for you. Please revert if this is not what you wanted. Lupp. Weren’t there your specific refreences previously?)
The formula above is working fine here. And it is case insensitive and works without enabling regular expressions …
What messy signature?
You may replace the UPPER(SearchString) by simply SearchString or yor reference to H$1 again if you can assure that the constant string or the referenced text are UpperCase anyway.
("Messy signature: In a preliminary view of the edited comment I got my avatar mixed up with your user name. This smoothed out automatically. I removed the note therefore again.)