Google Sheets Formulas

Recommended Readings

Item

Links

Documentation / Tutorials

Counting

// where $A:$A is your desiredrange
// if cell contains specific word
= countif($A:$A,"*_ALT*") // cannot be done with an arrayformula
// errors
=countif($A:$A,ISERROR())
// loading https://www.reddit.com/r/googlesheets/comments/aplwa6/importxml_loading/
=COUNTIF(ARRAYFORMULA(ISERR(A1:A1000)),true)
=TEXT(COUNTIF(ARRAYFORMULA(ISERR(A1:A1000)),false)/1000,"0%")

Misc

// if blank, no formula
= ARRAYFORMULA(IF(ISBLANK(A2:A),"",V2)
// join header row & other row by each cell with delimiter (2rows)
= ARRAYFORMULA($B$1:$K$1&" — "&B2:K2))
// split by line break, transpose vertically, sort, flatten by
= arrayformula(textjoin(char(10),1,sort(transpose(split(O2,char(10))))))

REGEX

// exclude first word
REGEXEXTRACT(trim(A2:A)," .+")
//first name
REGEXEXTRACT(A2,"^([\w\-]+)")
REGEXEXTRACT(A2, "\S+")
// match text and filter range
https://stackoverflow.com/questions/42846220/regexmatch-with-regex-reference-to-cell

Conditional formatting

Searching for errors

// custom formula
=iserror(B1)=true

Filtering

// list all matches from sheet 2 based on cell value
FILTER(sheet2!A:A, REGEXMATCH(sheets!A:A,A3))