0

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:

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?

Dave Bird
  • 3
  • 5
  • The string "day" is clearly not contained in a cell formatted as a date. So, you wouldn't find it. It's not clear to me that this situation is similar to looking for the string "day" in a date formatted field. If it is, I'd sure appreciate if someone explained. – Dave Bird Jun 30 '17 at 10:31
  • Yes, that I know. But, I don't want to go there unless someone can explain to me exactly how text and URLs are represented together in a cell. Also, I can use the find function to successfully return the location of the string in the URL containing cell. I just tried it. If no one gives me a good solution to my question, I'll use that to make a work-around. – Dave Bird Jun 30 '17 at 10:58

1 Answers1

0

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
Dave Bird
  • 3
  • 5