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

This one is fairly strait forward, please see the code below: =textjoin(",",true,arrayformula(address(row(B2:D4),column(B2:D4),4))) You can change the last 4 to a 1 if you want absolute references.


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

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

Does exactly what you asked, C alone is 8 and adds up only the C numbers. Per your example it only checks B1:D1 for C. =arrayformula(sum(iferror(if(SEARCH("C",B1:D1),if(len(B1:D1)=1,8,VALUE(substitute(upper(B1:D1),"C",""))),0),0)))


1

Answer: ... -> Edit chart -> Customize -> Gridlines -> Horizontal Axis (in drop down) -> Major gridline count Under the Major gridline count you can select or type however many dates you want to see on the horizontal label here, for auto mine shows up with a reasonable number of dates but if you wanted to see 20 dates or 10 dates you can type 20 or 10 ...


1

This is as simple as I could get using INDIRECT (RC notation): =SUM(INDIRECT("R"&ROW()-6&"C"&COLUMN()-1&":R"&ROW()&"C"&COLUMN()-1,)) However using OFFSET is much shorter (B7 being the cell the formula is in) =sum(offset(B7,-6,-1,7,1)) Just because I put B7 in the above formula doesn't mean it is not relative, it is, if you ...


1

I am the OP. I tried switch and it almost worked but I couldn't quite string it out right with that function. I searched through a function list however and found the IFS function and that seems to be working wonderfully. Thank you for trying to help pjmg. Sorry I didn't have more information and then quickly worked it out myself. I was stressed with trying ...


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

The following code will give you the exact output you are looking for in your bottom example: =arrayformula(if( query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc")="",0, query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '...


1

You need to use Curly Brackets for this. =VLOOKUP(MAX(L2:L6),{L2:L6,K2:K6},2,FALSE) You can even have more columns included =VLOOKUP(MAX(L2:L6),{L2:L6,K2:K6,M2:N6},2,FALSE) You can read more about Curly brackets


1

If you want it all in one cell you need to use Query: =query(importrange("https://docs.google.com/spreadsheets/d/1q87c_0rFdt8FcWtIzD0KVW3k1ZpuvhfeWb1281MEs","DEMOS!A2:C1000"),"select count(Col1) where Col2 = 'Rep1' and Col1 >= Date '2019-11-9' and Col1 <= Date '2019-11-15' label count(Col1) ''")


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

The following isn't an answer to your question per se, but it is an exploration of the syntax surrounding TextStyle and RichTextValue. It may assist in understanding why var x = A1.getRuns(); doesn't work, and how you might build code to analyse cells. Note the programmatic creation of RichText is another thing altogether. Spreadsheet Screenshot function ...


1

Unless you want to delve into the Advanced Google Services or Filter View, a "quick and dirty" workaround would be to rename your filters giving them a serial number: 01-myfilter, 02-yourfilter, 03-onemore. Rather a practical solution of re-naming than a "Google developer solution".


1

d/m/Y is an ambiguous date formatting. It's very likely that this occurs because the spreadsheet regional setting used in the spreadsheet doesn't support d/m/Y as a date formatting. Please note that the screenshot included in the question shows column J values having a "day" value greater than 12, with exception of 11/01/2019 have the a white cell ...


1

Another way to do it is to unhide the selection, then do the search but make sure not to make any changes or do anything else. When you finish the search, simply undo (ctl + z or command + z for mac) and this should rehide the selection without you needing to remember exactly which rows were selected. It worked well for me :)


1

=BDMIN(GOOGLEFINANCE("Goog"; "PRICE"; "YourStarDate";"YourEndDate");"CLOSE"; GOOGLEFINANCE("Goog"; "PRICE"; "YourStarDate";"YourEndDate")) It works for me. Example for the english locale: =DMIN(GOOGLEFINANCE("Goog", "PRICE", "10/10/2019","10/11/2019"),"CLOSE", GOOGLEFINANCE("Goog", "PRICE", "10/10/2019","10/11/2019"))


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