We’re rewarding the question askers & reputations are being recalculated! Read more.

# Tag Info

2

You have the basic concept right just piece it together step by step. This does the job: =REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR( REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE), "https://example.com/(\S+)"),""))),"\s+"," ") You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...

2

You can also use this single robust formula in cell B2: =IFERROR(ArrayFormula(if(LEN(A2:A),TRIM(SPLIT( REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","\$1"&" ")," ") ),"")),"") An even simpler formula can be used if you don't have any empty rows in ...

2

EDIT (following OP's comment) To exclude empty cells (which are counted as zeros altering the result) we adjust the formula by adding the FILTER function. =AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(FILTER(A44:H44,A44:H44<>"")),"L",""))*1)) Initial answer You can use this simple formula: =AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(A44:H44),"L",""...

1

In C2 enter this formula : =filter( ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A)), ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))<>"" ) Breakdown of the formula : =REGEXMATCH(A2,TEXTJOIN("|",true,B2:B)) Regexmatch returns TRUE or FALSE if A2 contains any of the words in column B. TEXJOIN ...

1

Yes you can do it. There are a lot of approaches for this problem. Example : =QUERY(A:B,"SELECT * WHERE "&C1&C2") Cell C1, C2 contains the rest of the query like C1 B = 'Apples' C2 OR B = 'Peaches' You can build C1 and C2 with conditions such as C1 =IF(D1<>"","B = '"&D1&"'","") Where D1 is your fruit's name Then build ...

1

We usually don't have to conceptualize it this way, but in the "conditions" part of FILTER, we're actually setting up an array of TRUE or FALSE results to control whether each input row makes it through the filter. Perhaps take advantage of that and negate the Booleans outside the REGEXMATCH? =FILTER(Planning!G\$5:G\$37; NOT(REGEXMATCH(Planning!G\$5:G\$37; "a"))...

1

Use regular expressions and REGEXMATCH I think that =REGEXMATCH(A1,"^\[.*]\$") should work.

1

Yes, it can. The formula in the question is very convoluted and there isn't a description of what it does, so here is a a simple formula that returns Saturday Sunday Monday Formula =ArrayFormula(ARRAY_CONSTRAIN(TEXT(A2:A,"dddd"),3,1)) Explanation TEXT(A2:A,"dddd") This returns the day of the week for all cells on A2:A ARRAY_CONSTRAIN is used to limit ...

1

https://www.english-corpora.org/coca/ search: I 'd be *ing Please notice, there should be a space between I and 'd.

1

Presuming you don't want to add another row, because who does, the below should work: =JOIN(";",FILTER( ArrayFormula(TRANSPOSE(\$C\$2:\$G\$2)&"|"&TRANSPOSE(C3:G3)), NOT(ISBLANK(TRANSPOSE(C3:G3))) )) \$C\$2:\$G\$2 is your Skill ID ROW Range C3:G3 is your individual Skill ID ROW range The gimmick here is that you just add a "|" to everything and use FILTER ...

1

Answering my own question because I managed to come up with a solution separate from Joel. It requires me using a slightly different approach to the data, but it may be helpful for people that approach the answer differently. I created a second row (row 35) below the Skill Level rows (row 34) in the above image. In that row I have the following formula: =...

1

Assuming your Skill columns are B through G (alter to suit), try this: =REGEXREPLACE(REGEXREPLACE(ARRAYFORMULA(JOIN(";",IF(NOT(LEN(B2:G2)),"",B\$1:G\$1&"|")&B2:G2)),";{2,}",";"),";\$","")` It does just what you said… check for non-blanks and return the found entries' pairs, then it uses regexreplaces to remove any consecutive or trailing semicolons.

1

It seems, the "aaa E: 111 111 bbb" string had special character instead of space. When I replaced the space in "111 111" the result became TRUE. So it was a special character instead space, this is why the formula was FALSE originally. Problem solved.

Only top voted, non community-wiki answers of a minimum length are eligible