Is it possible to extract pieces of text in a sentence containing X in a google sheet. In a Formula of via Apps script. I couldn't find a decent answer on google or didn't know what to search for.

For example:

A1: [Peter] kicks the [ball] to [Shaun].

I want to extract the first word containing '[' & ']' in B1, the second word containing '[' & ']' in C1, et cetera.

So:

CodePudding user response：

Try this

=regexextract(A1,regexreplace(regexreplace(A1,"[\[]","(\\\["),"[\]]","\\\])"))

CodePudding user response：

Extracting words and posting them to sheet

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let a = sh.getRange("A1").getValue();
  let o = a.toString().match(/\[[A-Za-z] \]/g).map(e => e);
  sh.getRange(1, 2, 1, o.length).setValues([o]);
  console.log(JSON.stringify(a));
}

Sheeto: