NoSleepCreative Wiki
  • Welcome to NoSleepCreative
  • After Effects
    • Getting Started with Expressions
    • Expressions & Snippets
      • JSX Cheatsheet
      • Expression Troubleshooting
      • Utilities
      • Shape & Mask
      • Type & Text
    • Cookbook
      • Algorithmic
      • Random properties
      • Harmonic Motion
      • Staggering
      • Tessellation & Tiling
      • Type animators
      • Speed lines
      • Radial Array
      • Orb & Trails
      • Shading & Texturing
      • Responsive
      • Automation
      • Setup & Rigs
    • Getting started with Scripting
    • Scripting
      • Utilities
      • Master Properties
    • ScriptUI
  • Studio Ops
    • Tooling
    • Toolkitting
    • Knowledge Base
    • Naming Convention
    • DAM
  • Cinema 4D
    • Formulas
    • Python Cheat Sheet
      • For Artists
      • Maya Environment
      • Maya snippets
      • VSFX 705
    • Cookbook
  • Info
    • About
    • Portfolio
    • Course
    • YouTube
    • Gumroad
    • GitHub
  • Dev
    • archive
      • Webscraping
      • Google Sheets Formulas
      • SQL
      • Terminal
      • C++
      • Unreal Engine
      • Concert Visualization
      • Dome-projection
      • UI UX
      • Professional Etiquettes
      • Woes
      • How to get better
        • Portfolio / Showreel
        • Design with cooking
      • Media theories
        • Post Cinematic Affect
        • Marxism, Reproduction and Aura
        • Heuristics & Authorship
        • 02 Semiotics
        • 3 Process?
        • 05
        • 06 Technology & Mediation
        • Formalism
        • Simulation
        • The Gaze & Media Critique
        • Import
        • 10-12
      • Recommended books
        • 🔴Things I learned
      • Mac Superuser
        • Applescript
      • InDesign
      • Illustrator
      • Blender
      • Premiere Pro
      • Mathematics
        • Probability
        • Linear Algebra
      • Shader Dev
      • Getting Started with After Effects
        • Best Practices
        • Pimping up AE
        • Environment
      • Houdini
        • Cheatsheet
        • Cookbook
        • Techniques
        • Dynamic
        • Rendering & Lighting
        • Animation
        • Particles
        • Others
          • Modeling
          • Fluids - Pyro & Smoke
          • Rendering
      • REGEX
    • Sandbox
      • Nexrender
        • Terminology
        • Project Files Preparation
Powered by GitBook
On this page
  • Recommended Readings
  • Counting
  • Misc
  • REGEX
  • Conditional formatting
  • Searching for errors
  • Comparing two columns

Was this helpful?

  1. Dev
  2. archive

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%")

// whitespace
=COUNTA(SPLIT(A1, " "))

// count containing based on cell
=countif(A:A,"*"&A2&"*")

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

// weekends where A1 is the first date in the column.
=or(WEEKDAY(A3)=1,WEEKDAY(A3)=7)

Comparing two columns

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

// compare 2 columns for MATCHES / DIFFERENCE
=ArrayFormula(IF(A2:A=C2:C,"MATCH","DIFFER"))
PreviousWebscrapingNextSQL

Last updated 1 year ago

Was this helpful?

Comparing two columns
Trim & Split functions