从Google Spreadsheets中的单元格中提取多个值(如果存在) [英] Extract multiple values if present from cell in Google Spreadsheets

问题描述

使用Google re2 https://github.com/google/re2/blob/master/doc/syntax.txt

Using Google re2 https://github.com/google/re2/blob/master/doc/syntax.txt

从几行，例如

1. 我爱摇滚
2. 我爱摇滚剪刀
3. 我讨厌纸
4. 我喜欢石头，纸和剪刀
5. 我会爱自己

我想提取"Rock"，"paper"和"scissors"作为"Rock"，"paper"和"scissors".从每一行.我希望正则表达式能够匹配以上所有五行，并在找到东西的地方给我石头，剪刀和剪刀.我主要在Google表格中使用它，但是任何Google re2正则表达式都应该有所帮助.

I want to extract "Rock", "paper"and "scissors" from each line. I want the regex to match all the above five lines and give me Rock, paper and scissors where it found something. I'm predominantly using this in Google sheets, but any Google re2 regex should help.

我尝试过:

".*(([Rock]{0,4})).*"

".*(([Rock]{4})|([Rock]{0})).*"

=REGEXEXTRACT(A2,".*(Rock{0,2}).*(paper{0,2}).*(scissors{0,2}).*")

和其他多种组合来从任何行中获取Rock，如果存在的话...但是，它总是更喜欢零而不是四...即使找到Rock，它也会返回空字符串.如果我将{0}替换为{1}，则会得到"k".即使找到了完整的岩石.

and multiple other combinations to get Rock from any line, if present... But, it always prefers zero rather than four... Even If it finds Rock , it returns empty strings. If i replace {0} with {1} i get "k" even though the full Rock is found.

有什么想法吗?

推荐答案

我发现了一些 regex功能.

请尝试以下解决方法:

=ArrayFormula(IFERROR(REGEXREPLACE(A3,REGEXREPLACE(A3,"(Rock|paper|scissors)","(.*)"),{"$1","$2","$3"})))

在步骤1中，此公式使步骤2成为正则表达式:

In step 1 this formula makes regex for step2:

这篇关于从Google Spreadsheets中的单元格中提取多个值(如果存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！