How to find partial matching elements in libreoffice spreadsheet?

1
votes

In an excel spreadsheet I have a column (column A) with e.g. the following elements:

Audi, VW, BMW Group, Toyota

Now I can do a search of an element like this

match("*"&b1&"*", a1:a5,0)

which will either return the relative number of where the element is found or N/A. So, for example, if I put Audi in the cell B1, the formula will return '1', and it I put 'BMW' in the cell B1, the formula returns the value '3' (since the formula uses wildcards).

However, in a libreoffice spreadsheet this does not work. When putting the term 'BMW' into cell B1, the formula here returns 'N/A'. How to change the formula to have it work the same as the excel spreadsheet?

1
libreoffice-calc
I believe the pattern matching syntax for Calc is match(".*"&b1&".*", a1:a5,0). - user4039065
No this does not work. Now searching for either 'BMW' or 'Audi' always returns 'N/A'. - Alex
Are you sure that Regex is activated? See Tools->Options... OpenOffice.org Calc... Calculate ... Checkbox 'Enable regular expressions in formulas' - Fratyx

1 Answers

2
votes

The syntax I provided in my comment above used commas to deliminate the parameters; those probably should have been semi-colons. Here is the proof on Calc 4.1.1 with Tools ► Options ► OpenOffice Calc ► Calculate ► Enable regular expressions in formulas turned ON.

   enter image description here