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

# Tag Info

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 ...

1

The formula that would give you the desired results would be: =ArrayFormula(REGEXREPLACE(ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B3:H8),B3:H8&"★",)&REPT(" "&"♣︎",COLUMN(B3:H3)=COLUMN(H3))),"♣︎")))),"([^★]+).+","\$1")) It is a bit convoluted but works fine.

1

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

try this. It's cumbersome but it works =average(if(isnumber(A1),A1,value(left(A1,len(A1)-1))),if(isnumber(B1),B1,value(left(B1,len(B1)-1))),if(isnumber(C1),C1,value(left(C1,len(C1)-1))),if(isnumber(D1),D1,value(left(D1,len(D1)-1))),if(isnumber(E1),E1,value(left(E1,len(E1)-1))),if(isnumber(F1),F1,value(left(F1,len(F1)-1))),if(isnumber(G1),G1,value(left(G1,len(...

1

The LEFT function is for you to find values from the left-to-right summation and the RIGHT function does just the opposite. The FIND function is to find at what position exactly text you want. It automatically starts counting characters for LEFT or RIGHT. And the LEN function is to count the total value of characters that exist in a cell, so you need to use ...

1

You can try =switch : =SWITCH(A1:A10,"A",0,"B",1,"C",2) Result : Same result with regexmatch : =ArrayFormula( if(REGEXMATCH(A1:A10,"A"),0, if(REGEXMATCH(A1:A10,"B"),1, if(REGEXMATCH(A1:A10,"C"),2,"no match"))) )

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