在Google表格公式中使用RegEx提取多个值 [英] Extracting multiple values with RegEx in a Google Sheet formula
问题描述
我有一个包含2列的Google电子表格.
I have a Google spreadsheet with 2 columns.
第一个单元格的每个单元格都包含JSON数据,如下所示:
Each cell of the first one contains JSON data, like this:
{
"name":"Love",
"age":56
},
{
"name":"You",
"age":42
}
然后我想要第二列,该列将使用公式提取名称的每个值并将其像这样字符串化:
Then I want a second column that would, using a formula, extract every value of name and string it like this:
Love,You
现在我正在使用以下公式:
Right now I am using this formula:
=REGEXEXTRACT(A1, CONCATENER(CHAR(34),"name",CHAR(34),":",CHAR(34),"([^",CHAR(34),"]+)",CHAR(34),","))
RegEx表达式为"name":([[^"] +),
The RegEx expresion being "name":"([^"]+)",
问题在于它当前仅返回第一次出现的情况,像这样:
The problem being that it currently only returns the first occurence, like this:
Love
(而且,我不知道名称"的出现次数.可能在0到20之间.)
(Also, I don't know how many occurences of "name" there are. Could be anywhere from 0 to around 20.)
是否有可能实现我想要的目标?
Is it even possible to achieve what I want?
非常感谢您的阅读!
我的JSON数据始于:
My JSON data starts with:
{
"time":4,
"annotations":[
{
然后在中间,像这样:
{
"name":"Love",
"age":56
},
{
"name":"You",
"age":42
}
结尾为:
],
"topEntities":[
{
"id":247120,
"score":0.12561166,
"uri":"http://en.wikipedia.org/wiki/Revenue"
},
{
"id":31512491,
"score":0.12504959,
"uri":"http://en.wikipedia.org/wiki/Wii_U"
}
],
"lang":"en",
"langConfidence":1.0,
"timestamp":"2020-05-22T12:17:47.380"
}
推荐答案
由于您的文本基本上是JSON字符串,因此您可以使用以下自定义函数解析其中的所有 name
字段:
Since your text is basically a JSON string, you may parse all name
fields from it using the following custom function:
function ExtractNamesFromJSON(input) {
var obj = JSON.parse("[" + input + "]");
var results = obj.map((x) => x["name"])
return results.join(",")
}
然后将其用作 = ExtractNamesFromJSON(C1)
.
如果需要正则表达式,请使用类似的方法:
If you need a regex, use a similar approach:
function ExtractAllRegex(input, pattern,groupId,separator) {
return Array.from(input.matchAll(new RegExp(pattern,'g')), x=>x[groupId]).join(separator);
}
然后将其用作 = ExtractAllRegex(C1,""name":"([^^"] +)",1,",)
.
注意:
-
输入
-当前单元格值 -
模式
-正则表达式模式 -
groupId
-捕获要提取的组ID -
分隔符
-用于加入匹配结果的文本.
input
- current cell valuepattern
- regex patterngroupId
- Capturing group ID you want to extractseparator
- text used to join the matched results.
这篇关于在Google表格公式中使用RegEx提取多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!