Finding a substring

Discuss the spreadsheet application

Finding a substring

Postby lpenet » Thu Jan 10, 2008 1:11 pm

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,
lpenet
 
Posts: 1
Joined: Thu Jan 10, 2008 1:06 pm

Re: Finding a substring

Postby acknak » Thu Jan 10, 2008 10:28 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17420
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding a substring

Postby kingfisher » Fri Jan 11, 2008 3:54 am

What about using FIND or SEARCH? Either function can be combined with ISNUMBER if you want to avoid an error result.
OpenOffice 3.3 on PCLinuxOS. There are 3 kinds of people: those who can count and those who can't.
User avatar
kingfisher
Volunteer
 
Posts: 1861
Joined: Tue Nov 20, 2007 10:53 am

Re: Finding a substring

Postby Villeroy » Fri Jan 11, 2008 10:52 am

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding a substring

Postby kingfisher » Fri Jan 11, 2008 11:34 am

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
OpenOffice 3.3 on PCLinuxOS. There are 3 kinds of people: those who can count and those who can't.
User avatar
kingfisher
Volunteer
 
Posts: 1861
Joined: Tue Nov 20, 2007 10:53 am

Re: Finding a substring

Postby Villeroy » Fri Jan 11, 2008 1:28 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding a substring

Postby acknak » Fri Jan 11, 2008 7:34 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17420
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Finding a substring

Postby Villeroy » Fri Jan 11, 2008 8:22 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17306
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding a substring

Postby acknak » Fri Jan 11, 2008 9:45 pm

Interesting; thanks.

It seems this is getting some thought, which is good: Issue 32344: Simple wildcards * and ? as opposed to regular expressions.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17420
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: Richarda44 and 32 guests