如何在包含链接的单元格中找到字符串？

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.

我在openoffice calc中有一些包含链接/ URL的单元格。当然,它们以calc形式显示为文本,并且鼠标悬停显示URL。单击这些单元格会显示引用的URL。

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

单元格A1包含搜索的字符串。单元格A4:A7包含链接/ URL。单元格B4:B7是A4:A7的副本,但使用默认格式删除链接/ URL。单元格B3包含我的匹配公式,它成功地在B4:B7中找到字符串。我在单元格A3中尝试了以下内容来查找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?

此外,尝试了其他几个正则表达式,其中没有一个工作。是的,我在正则表达式上生锈了,但我做错了什么?或者,除非我更改格式,否则文字字符串实际上不存在于搜索字段中?

1 个解决方案

#1

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.

在工具 - >选项 - > 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.

关闭此设置可使所有内容按照广告宣传。线索是正则表达式“。*”和A1&“。*”,当然匹配一整行纯文本,使用范围B4:B7。

The simplest solution is the expression:

最简单的解决方案是表达式:

=MATCH(""&A1;A4:A7;0) # "" invoked to trigger regex

#1