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

Hot answers tagged

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

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


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