After Effects
Code
Software & Tutorials
Google Sheets Formulas
Recommended Readings
Item
Links
Documentation / Tutorials
Counting
1
// where $A:$A is your desiredrange
2
3
// if cell contains specific word
4
= countif($A:$A,"*_ALT*") // cannot be done with an arrayformula
5
6
// errors
7
=countif($A:$A,ISERROR())
8
9
// loading https://www.reddit.com/r/googlesheets/comments/aplwa6/importxml_loading/
10
=COUNTIF(ARRAYFORMULA(ISERR(A1:A1000)),true)
11
=TEXT(COUNTIF(ARRAYFORMULA(ISERR(A1:A1000)),false)/1000,"0%")
12
13
// whitespace
14
=COUNTA(SPLIT(A1, " "))
15
16
// count containing based on cell
17
=countif(A:A,"*"&A2&"*")
Copied!
Misc
1
// if blank, no formula
2
= ARRAYFORMULA(IF(ISBLANK(A2:A),"",V2)
3
4
// join header row & other row by each cell with delimiter (2rows)
5
= ARRAYFORMULA($B$1:$K$1&" — "&B2:K2))
6
7
// split by line break, transpose vertically, sort, flatten by
8
= arrayformula(textjoin(char(10),1,sort(transpose(split(O2,char(10))))))
Copied!
REGEX
1
// exclude first word
2
REGEXEXTRACT(trim(A2:A)," .+")
3
4
//first name
5
REGEXEXTRACT(A2,"^([\w\-]+)")
6
REGEXEXTRACT(A2, "\S+")
7
8
// match text and filter range
9
https://stackoverflow.com/questions/42846220/regexmatch-with-regex-reference-to-cell
Copied!
Conditional formatting
Searching for errors
1
// custom formula
2
=iserror(B1)=true
3
4
// weekends where A1 is the first date in the column.
5
=or(WEEKDAY(A3)=1,WEEKDAY(A3)=7)
Copied!
Comparing two columns
1
// list all matches from sheet 2 based on cell value
2
FILTER(sheet2!A:A, REGEXMATCH(sheets!A:A,A3))
3
4
// compare 2 columns for MATCHES / DIFFERENCE
5
=ArrayFormula(IF(A2:A=C2:C,"MATCH","DIFFER"))
Copied!
Last modified 1yr ago