openoffice calc - How to find partial matching elements in excel spreadsheet? -

openoffice calc - How to find partial matching elements in excel spreadsheet? -

in excel spreadsheet have column (column a) e.g. next elements:

audi, vw, bmw group, toyota

now can search of element this

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

which either homecoming relative number of element found or n/a. so, example, if set audi in cell b1, formula homecoming '1', , set 'bmw' in cell b1, formula returns value '3' (since formula uses wildcards).

however, in libreoffice spreadsheet not work. when putting term 'bmw' cell b1, formula here returns 'n/a'. how alter formula have work same excel spreadsheet?

wildcard charter in query string may work.

try next function , set 'bmw' cell b1.

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

'bmw*' matches 'bmw', 'bmw group', 'bmw car' , other cell contents begin 'bm'.

if want match string contains 'bmw', utilize wildcard character @ both end of value.

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

excel openoffice-calc libreoffice-calc