Skip to main content
added variation and function links
Source Link
Blindspots
  • 7.5k
  • 5
  • 20
  • 38

Depending how your routines are named and how they are joined in the strings there may be a few different ways to get the individual values for each property.

Formula based on your sample data "Value \d"

=LET(rng,A1:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str,
     TOROW(MAP(INDEX(REGEXEXTRACT(str, REGEXREPLACE(str, "(Value \d)", "($1)"))),
       LAMBDA(routine, {name; routine})),,1))),1),2))

Formula that uses a range of lookup values

Screenshot showing formula and results in Google Sheets Assuming you have a list of routine names in Column D, the regular expression could instead be

"("& TEXTJOIN("|", 1, D:D) &")"

So the formula would then be

=LET(rng,A:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str, 
     TOROW(MAP(INDEX(REGEXEXTRACT(str, 
       REGEXREPLACE(str, "("& TEXTJOIN("|", 1, D:D) &")", "($1)"))),
          LAMBDA(routine, {name; routine})),,1))),1),2))

Screenshot showing formula and results in Google Sheets

FILTER  INDEX  LAMBDA  LEN  LET  MAP  REGEXEXTRACT  REGEXREPLACE  TEXTJOIN  TOROW  WRAPROWS

=LET(rng,A1:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str,
     TOROW(MAP(INDEX(REGEXEXTRACT(str, REGEXREPLACE(str, "(Value \d)", "($1)"))),
       LAMBDA(routine, {name; routine})),,1))),1),2))

Screenshot showing formula and results in Google Sheets

Depending how your routines are named and how they are joined in the strings there may be a few different ways to get the individual values for each property.

Formula based on your sample data "Value \d"

=LET(rng,A1:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str,
     TOROW(MAP(INDEX(REGEXEXTRACT(str, REGEXREPLACE(str, "(Value \d)", "($1)"))),
       LAMBDA(routine, {name; routine})),,1))),1),2))

Formula that uses a range of lookup values

Assuming you have a list of routine names in Column D, the regular expression could instead be

"("& TEXTJOIN("|", 1, D:D) &")"

So the formula would then be

=LET(rng,A:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str, 
     TOROW(MAP(INDEX(REGEXEXTRACT(str, 
       REGEXREPLACE(str, "("& TEXTJOIN("|", 1, D:D) &")", "($1)"))),
          LAMBDA(routine, {name; routine})),,1))),1),2))

Screenshot showing formula and results in Google Sheets

FILTER  INDEX  LAMBDA  LEN  LET  MAP  REGEXEXTRACT  REGEXREPLACE  TEXTJOIN  TOROW  WRAPROWS

Source Link
Blindspots
  • 7.5k
  • 5
  • 20
  • 38

=LET(rng,A1:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str,
     TOROW(MAP(INDEX(REGEXEXTRACT(str, REGEXREPLACE(str, "(Value \d)", "($1)"))),
       LAMBDA(routine, {name; routine})),,1))),1),2))

Screenshot showing formula and results in Google Sheets