regex - 从范围中提取与“模式”匹配的所有行

Question

我对此很陌生，我用谷歌搜索了正则表达式和 RE2，但几乎没有找到帮助。似乎是一个相当小众的话题，而且材料不是很全面。将不胜感激任何帮助。谢谢！

我有一个包含超过 30k 行文本的列表，我想提取与此模式匹配的那些：

`=whatever`+whatever-whatever`+whatever/whatever.whatever

数据如下所示：

Detail 3
Detail 4
Detail 3
Detail 4
blah
blah
Detail 5
Detail 4
Detail 5
`=P2385`+P2385-M01 `+MC2385/5.0
`=BS2366`+P2366-A1 `+FIELD/14.5
`=P2385`+P2385-M02 `+MC2385/5.4
blah
blah
`=P2385`+P2385-M03 `+MC2385/5.5
`=P2385`+P2385-M04 `+MC2385/5.9
`=P2385`+P2385-M05 `+MC2385/6.0
`=BS2366`+P2366-A1 `+FIELD/14.5
`=P2385`+P2385-M06 `+MC2385/6.4
`=P2385`+P2385-M07 `+MC2385/6.5
`=P2385`+P2385-M08 `+MC2385/6.9
blah
blah
`=P2385`+P2385-M09 `+MC2385/7.0
`=P2385`+P2385-M10 `+MC2385/7.4
`=P2385`+P2385-M11 `+MC2385/7.5
`=P2385`+P2385-M12 `+MC2385/7.9
`=P2381`+P2381-B31 `+MC2381/12.5
blah
blah
`=P2381`+P2381-B32 `+MC2381/12.9
`=P2381`+P2381-B33 `+MC2381/13.0
`=P2370`+P2370-M01
blah
blah
`+MC2370/6.0
`=P2370`+P2370-M02 `+MC2370/6.4
`=P2370`+P2370-M03 `+MC2370/6.5
`=P2370`+P2370-M04 `+MC2370/6.9
`=BS2366`+P2366-A1 `+FIELD/14.5
`=P2368`+P2368-M01 `+MC2370/11.0
`=P2368`+P2368-M05 `+MC2370/12.0
`=BS2366`+P2366-A1 `+FIELD/14.5 `=P2366`+P2366-M01 `+MC2370/10.5
`=P2366`+P2366-M02 `+MC2370/10.9
Detail 3
Detail 4
Detail 3
blah
blah
Detail 4
Detail 5
Detail 5
blah
blah
Detail 4
Detail 5
blah
blah
blah
blah

正则表达式提取的输出应该如下所示：

`=P2385`+P2385-M01 `+MC2385/5.0
`=P2385`+P2385-M02 `+MC2385/5.4
`=P2385`+P2385-M03 `+MC2385/5.5
`=P2385`+P2385-M04 `+MC2385/5.9
`=P2385`+P2385-M05 `+MC2385/6.0
`=BS2366`+P2366-A1 `+FIELD/14.5
`=P2385`+P2385-M06 `+MC2385/6.4
`=P2385`+P2385-M07 `+MC2385/6.5
`=P2385`+P2385-M08 `+MC2385/6.9
`=P2385`+P2385-M09 `+MC2385/7.0
`=P2385`+P2385-M10 `+MC2385/7.4
`=P2385`+P2385-M11 `+MC2385/7.5
`=BS2366`+P2366-A1 `+FIELD/14.5
`=P2385`+P2385-M12 `+MC2385/7.9
`=P2381`+P2381-B31 `+MC2381/12.5
`=P2381`+P2381-B32 `+MC2381/12.9
`=P2381`+P2381-B33 `+MC2381/13.0
`=P2370`+P2370-M02 `+MC2370/6.4
`=P2370`+P2370-M03 `+MC2370/6.5
`=P2370`+P2370-M04 `+MC2370/6.9
`=P2368`+P2368-M01 `+MC2370/11.0
`=P2368`+P2368-M05 `+MC2370/12.0
`=BS2366`+P2366-A1 `+FIELD/14.5
`=P2366`+P2366-M01 `+MC2370/10.5
`=P2366`+P2366-M02 `+MC2370/10.9

标签： regexgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin

Answer 1

=ARRAYFORMULA(TRANSPOSE(SPLIT(SUBSTITUTE("♦"&TEXTJOIN("♦", 1, 
 IFERROR(REGEXEXTRACT(A1:A, "`.+\+.+-.+\+.+/.+"))), " `=", "♦`="), "♦")))