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


2

Use the concatenation operator & and IF Example =QUERY({ 'ALPHA'!A6:X70; 'BETA'!A6:X70; 'CHARLIE'!A6:X69 },"select * where Col1 <>''" &IF(FALSE," order by Col24 DESC",)) Explanation The formula syntax doesn't include something similar to the PHP # comment, JavaScript // comment, /* */ multiple line comment or HTML <!-- -->, but you ...


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


1

Determine column alphabet letter as follows: =char(64+MATCH(N,1:1,0)) where you substitute the number you want for N=4,5...10. Then add that to the query string formulaically. Done: =QUERY(A1:D4,"select "&char(64+MATCH(5,1:1,0))&" where A = 'Speed'") Important limitation for generalisations: this only works for column index up to 27, because it ...


1

You can use the filter function with REGEXMATCH as a parameter, try this in C1 : =FILTER(B2:B100,arrayformula(REGEXMATCH(B2:B100,TEXTJOIN("|",true,A2:A4)))) It filter the range B2:B100 based on which result returns the regex Explanation : The following formula returns False or True based on the content in the cells B2:B100. The operator | is basically a ...


1

There isn't a built-in feature to protect or edit the protection of several sheets at once but Google Apps Script could be used to automated this task or an add-on created by yourself or by someone else. Reference Use Add-ons & Apps Script Extending Google Sheets Related Script to add protection and remove the user running it?


1

It was a syntax issue, my data range was incorrect.


1

I added the custom function: function getRangeAddress(namedRange) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(namedRange); return range.getA1Notation(); } and to use it, I did =getRangeAddress(WidgetTypes) and it returns C5:M5 as a string.


1

There isn't a built-in function that returns the address of a named range. One alternative is to use a custom function. The custom function should parse the cell formula to get the name of the named range then the code code could use that string to get the related range and after that you could use getA1Notation. Related How to pass a range into a custom ...


1

First formula : Don't use =!A1:A or =!A1 these are not ranges or cell addresses. The operator ! is used to reference a range in a specific tab like this : =Sheet1!A1 Using =A:A the sumif works. In E3 : =sumif(A:A,">=2/1/2018",B:B) Second formula error : Argument must be a range. It's because filtering B:B breaks the formula in the third argument ...


1

You are trying to associate a "text value " in a data validation cell with a given numerical value. One supposes that this is so that you can use the numerical value in another formula. It is common practice in forms for each select item to have a description AND a separate "value", unfortunately Google Sheets does not offer this. The cell that you use for ...


1

It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more). I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...


1

Since you are essentially using an ENUM in Google Sheets and the index is increasing by exactly one in each case, your best bet is to use arrays. If you had a more complicated mapping between integers to strings, you should use the SWITCH function. Here are examples in both directions (Int to String, and String to Int)... Option 1: via arrays (preferred) ...


1

You can hide rows just for yourself in a sense by creating a filter view that hides the rows you're not interested in. Others will be able to see & use the filter view, but it won't be their default view unless they choose it to be. Hiding columns doesn't seem to be possible with this method as of October 2019.


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