I have some cells in openoffice calc which contain links/URLs. They display, of course, in calc as text, and hovering the mouse shows the URL. Clicking on those cells brings up the URL referenced.
I want to match a string in the displayed text. The below shows the spreadsheet:
Cell A1 contains the string searched for. Cells A4:A7 contain the links/URLs. Cells B4:B7 are copies of A4:A7 but with Default format to remove the link/URLs. Cell B3 contains my match formula, which successfully finds the string in B4:B7. I've tried the following in cell A3 to find the string in A4:A7
`=MATCH("^"&A1&".*";B4:B7;0)` #only works on the default formatted cells.
`=MATCH(".*"&A1&".*";A4:A7;0)` #
`=MATCH(A1&".*";A4:A7;0)` #
`=MATCH(A1;A4:A7;0)` #
Also, tried several other regular expressions, none of which work. Yes, I'm rusty on regex's, but what am I doing wrong? Or, is the literal string actually not present in the search field unless I change the format?
All the problems with the searches were caused by the fact that
'Search criteria = and <> must apply to whole cells'
was enabled in Tools->Options->Openoffice Calc->Calculate.
Turning this setting off makes everything work as advertised. The clue was that the regex ".*"&A1&".*", which of course matches a full line of plain text, worked with the range B4:B7.
The simplest solution is the expression:
=MATCH(""&A1;A4:A7;0) # "" invoked to trigger regex