在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 value
• pattern - regex pattern
• groupId - Capturing group ID you want to extract
• separator - text used to join the matched results.

这篇关于在Google表格公式中使用RegEx提取多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！