Search code examples
libreoffice-calc

How to find partial matching elements in libreoffice spreadsheet?

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?

Solution

  • 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