How to negate ‘isblank’ function
I suggest: =not(isblank(A1)) which returns TRUE if A1 is populated and FALSE otherwise. Which compares with: =isblank(A1) which returns TRUE if A1 is empty and otherwise FALSE.
google-sheets
How do I avoid the “#DIV/0!” error in Google docs spreadsheet?
Wrap your formula with IFERROR. =IFERROR(yourformula)
Download link for Google Spreadsheets CSV export – with Multiple Sheets
Every document in Google Sheets supports the “Chart Tools datasource protocol”, which is explained (in a rather haphazard way) in these articles: “Creating a Chart from a Separate Spreadsheet” “Query Language Reference” “Implementing the Chart Tools Datasource Protocol” To download a specific sheet as a CSV file, replace {key} with the document’s ID and {sheet_name} … Read more
Check cell for a specific letter or set of letters
You can use RegExMatch: =IF(RegExMatch(A1;”Bla”);”YES”;”NO”)
Displaying Lakhs and Crores in Google Sheets
This works in Google Sheets for sure. Should work in Excel too. With the appropriate cells selected, navigate to: Google Sheets : Format -> Number -> More Formats -> Custom Number Format Excel : Format -> Cells -> Custom -> [Custom text box] Enter one of the following and Apply: A] For Lakhs and Crores … Read more
Google SpreadSheet Query: Can I remove column header?
Try this: =QUERY(H4:L35,”select sum(L) where H=’First Week’ label sum(L) ””)
Count rows with not empty value
I just used =COUNTIF(Range, “<>”) and it counted non-empty cells for me.
Apply formula to the entire column
It looks like some of the other answers have become outdated, but for me this worked: Click on the cell with the text/formula to copy Shift+Click on the last cell to copy to Ctrl + Enter (Cmd + Enter on Mac.) (Note that this replaces text if the destination cells aren’t empty)
Get first item from Split()
You can use the index function to select which value to return. So to retrieve the second value from your example you could use: =index(SPLIT(“1.23/1.15”, “https://stackoverflow.com/”), 0, 2) The last argument says which column you wish to retrieve – 1 would retrieve the first value. Alternatively you could use left / right and find to … Read more
Import data into Google Colaboratory
An official example notebook demonstrating local file upload/download and integration with Drive and sheets is available here: https://colab.research.google.com/notebooks/io.ipynb The simplest way to share files is to mount your Google Drive. To do this, run the following in a code cell: from google.colab import drive drive.mount(‘/content/drive’) It will ask you to visit a link to ALLOW … Read more