Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

New answers tagged

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


3

With a regex, you can replace each character with itself + ','. Then use a regular split function. =split(REGEXREPLACE(A1,"(.)","$0,"),",",true,true)


2

If I remember my highschool days, your expression reads : Sum of the n from 1 to 30. I.E. 1000*1.07^1 + 1000*1.07^2 + 1000*1.07^3 + ... Here's a way to do it : =sum(ArrayFormula(1000*power(1.07,(ROW(A1:A30))))) Result is 101 073.04


0

This can be done without hardcoding the columns in the formula. I am going to show you how to make a formula that can work with any number of columns without any change to the formula. We are going to have the filter checkboxes in the first row. I filled the whole first row with checkboxes. Note that in Google Sheets we can refer to the whole first row as ...


1

(In your example spreadsheet you have no formulas) Most likely your formula calculated results are given in TEXT format, so AVERAGE -which needs numbers to make calculations- can NOT give any results and you end up with #DIV/0!. A quick and easy fix: To change text cells to number cells, select your range (C2:C) and choose a number format for it by ...


0

You can use the following two formulas (no extra helper columns needed): For column B: Place this formula in cell B1 =QUERY(({A1:A5;"HELLO";A6:A10;"HELLO";A11:A15;"HELLO";A16:A20;"HELLO";A21:A25}),"SELECT *") An easy way to write/extend the formula is to copy the cluster ";HELLO";A21:A25 and repeat it for as long as you need it. For column C: Place this ...


1

Formula =ArrayFormula(MMULT(TRANSPOSE((ROW(B1:B9)<=TRANSPOSE(ROW(B1:B9)))*ISTEXT(B1:B9)*1),ISTEXT(B1:B9)*1)) What you are looking to do is called "running total". The above formula use arrays, TRANSPOSE, MMULT (matrix multiplication), ISTEXT and unity multiplication (to parse TRUE as 1 and FALSE as 0) instead creating a COUNTA formula chain. The ...


0

Try this and expand it : =COUNTA(INDIRECT("B1:B"&ROW(B1))) Note : =ArrayFormula(COUNTA(INDIRECT("B1:B"&ROW(B1:B6)))) is Arrayformula ( COUNTA ( =INDIRECT(B1:B{1;2;3;4;5;6} ) ) =INDIRECT() Returns a cell reference specified by a string. Source


0

Here's my solution in a worksheet without using Arrayformula as much as possible. I haven't found a way to avoid having to drag down column B, but that's the only thing that's not perfect. You can delete cells in column A and all the formulae stay intact, as requested: The formula for Column B is (copy this cell into B1 and drag down) =IF(ISERROR(MATCH("...


Top 50 recent answers are included