Finding a substring

Discuss the spreadsheet application
Post Reply
lpenet
Posts: 1
Joined: Thu Jan 10, 2008 1:06 pm

Finding a substring

Post by lpenet »

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,
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding a substring

Post by acknak »

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Finding a substring

Post by kingfisher »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding a substring

Post by Villeroy »

kingfisher wrote:What about using FIND or SEARCH? Either function can be combined with ISNUMBER if you want to avoid an error result.
If EQIV is MATCH then lpenet wants to match cell values rather than strings within cells.
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
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Finding a substring

Post by kingfisher »

I was looking at the thread title and these words in the o.p. (emphasis added):
function that would search not for an exact match but for a substring.
I took 'substring' to mean part of the text.

BTW, I hope my avatar does not eat your avatar; that would be bad manners. :o
Apache OpenOffice 4.1.9 on Linux
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding a substring

Post by Villeroy »

kingfisher wrote:I was looking at the thread title and these words in the o.p. (emphasis added):
function that would search not for an exact match but for a substring.
I took 'substring' to mean part of the text.
But he tries to lookup a whole cell, having a substring:
=INDIRECT(ADRESSE(....
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.
BTW, I hope my avatar does not eat your avatar; that would be bad manners. :o
The kingfisher did satisfy his hunger already. I can offer plenty of butterflies.
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
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding a substring

Post by acknak »

Villeroy wrote:Currently there is no way to enable matching by wildards in Calc.
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?

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding a substring

Post by Villeroy »

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
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding a substring

Post by acknak »

Interesting; thanks.

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
Post Reply