Dear all,
I am looking for an equivalent of the "EQUIV" function that would search not for an exact match but for a substring.
I use it in the following (in French) formula :
=INDIRECT(ADRESSE(LIGNE(C44);COLONNE(C44)+(ARRONDI.INF((EQUIV($D3;C65:BV65;0)-1)/4)*4)))
So, this function would have to return the index of the cell in the specified range containing the substring $D3.
Thanks in advance for your help !
Regards,
Finding a substring
Re: Finding a substring
MATCH() ("EQUIV" en Français) can use a pattern (wildcard; regular expression) search, so it can do a substring match. If you want to use the contents of $D3 as the substring, you can use something like this:
EQUIV(".*" & $D3 & ".*"; C65:BV65; 0)
One other thing: you might want to use OFFSET() ("DECALER") instead of INDIRECT to convert the result of MATCH to a cell reference. It is simpler to design and less prone to falling apart later on if things get moved around.
PS: If D3 should contain punctuation characters, the pattern match may fail badly. If that is a possibility, you'll need to process the text in D3 to fix the special characters.
EQUIV(".*" & $D3 & ".*"; C65:BV65; 0)
One other thing: you might want to use OFFSET() ("DECALER") instead of INDIRECT to convert the result of MATCH to a cell reference. It is simpler to design and less prone to falling apart later on if things get moved around.
PS: If D3 should contain punctuation characters, the pattern match may fail badly. If that is a possibility, you'll need to process the text in D3 to fix the special characters.
AOO4/LO5 • Linux • Fedora 23
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: Finding a substring
What about using FIND or SEARCH? Either function can be combined with ISNUMBER if you want to avoid an error result.
Apache OpenOffice 4.1.9 on Linux
Re: Finding a substring
If EQIV is MATCH then lpenet wants to match cell values rather than strings within cells.kingfisher wrote:What about using FIND or SEARCH? Either function can be combined with ISNUMBER if you want to avoid an error result.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: Finding a substring
I was looking at the thread title and these words in the o.p. (emphasis added):
BTW, I hope my avatar does not eat your avatar; that would be bad manners.
I took 'substring' to mean part of the text.function that would search not for an exact match but for a substring.
BTW, I hope my avatar does not eat your avatar; that would be bad manners.
Apache OpenOffice 4.1.9 on Linux
Re: Finding a substring
But he tries to lookup a whole cell, having a substring:kingfisher wrote:I was looking at the thread title and these words in the o.p. (emphasis added):
I took 'substring' to mean part of the text.function that would search not for an exact match but for a substring.
Acknak's method should work. But (of course) it depends on a certain setting. Tools>Options>Calculation>"Regular expressions in formulas", which is set by default, but as soon as you save the file as xls or open an xls file it is unset since Excel only knows plain wildcards ?*. Currently there is no way to enable matching by wildards in Calc.=INDIRECT(ADRESSE(....
The kingfisher did satisfy his hunger already. I can offer plenty of butterflies.BTW, I hope my avatar does not eat your avatar; that would be bad manners.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Finding a substring
BTW, did you mean "Excel" at the end there? I'm not sure what you're saying. Does Excel handle wildcard matches, but in a way that is incompatible with Calc, or does Excel simply not do them at all?Villeroy wrote:Currently there is no way to enable matching by wildards in Calc.
At any rate, you make an excellent point. I often forget about compatibility with Excel because I don't have it and I don't recommend that anyone exchange working files between the two--at least not without some very careful thought. There are far too many ways for it to go badly wrong, as you just pointed out.
AOO4/LO5 • Linux • Fedora 23
Re: Finding a substring
Yes, Excel handles simple wildcards similar to shell patterns. MATCH("?ron*";range) matches anything starting with aron, bron, cron, ... OpenOffice.org has no switch to enable simple pattern matching. If regular expressions in formulas are disabled, "?ron*" matches literal, case insensitive "?ron*", "?Ron*", "?rOn*".
Last edited by Villeroy on Fri Jan 11, 2008 10:22 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Finding a substring
Interesting; thanks.
It seems this is getting some thought, which is good: Issue 32344: Simple wildcards * and ? as opposed to regular expressions.
It seems this is getting some thought, which is good: Issue 32344: Simple wildcards * and ? as opposed to regular expressions.
AOO4/LO5 • Linux • Fedora 23