Spreadsheet Secrets: Underrated Spreadsheet Functions Pt.1
Very useful Functions that are in Google Sheets and not in Excel. Including Tools and Methods for Processing ChatGPT4 Outputs. Regex Functions; Currency Conversion; Convert to chip; Open multiple URLs
This is the first part of a planned four-part series on my recommended tech hacks. They are (what I believe to be) relatively simple, accessible tech hacks that I personally find useful. I believe many of these are highly underrated. The upcoming parts will be: “Underrated Spreadsheet Functions pt.2 In both Excel and Sheets”; “Optimizing Desktop: Chrome, Docs, Windows”; Optimizing Android Smartphone”. See also my previous piece on my mobile setup: “Mobile Minimalism: Streamlining My Smartphone Layout” (July 5, 2023)
Regex Functions
Very powerful. I now use this extensively, for both work, and my research. I ask ChatGPT4 to produce it for me, with natural language. I give input, and expected output. Then I iterate, using actual output, and expected output.
Split by letter, for Transliteration
=JOIN("", ARRAYFORMULA(XLOOKUP(SPLIT(REGEXREPLACE(A1, "(.)", "$1,"), ","), C:C, D:D, "", 1)))
Explained in this piece:
Extract text that immediately follows a closing parenthesis (‘)’) and continues until it reaches a period (‘.’).
REGEXEXTRACT(A1, "\)\s*([^\.]+)")
This regular expression will match and extract text that immediately follows a closing parenthesis (‘)’) and continues until it reaches a period (‘.’). It will ignore any whitespace characters immediately following the closing parenthesis. I used this to automatically extract the Jastrow definition.
Example input:
(ὀπτίων, optio; Perl. Et. St. p. 103; D. C. Lat. s. v.) commissary, quartermaster in the Roman army. Y. Sabb. VI, 8^cbot. אתא א' וכ' a Roman quarterm. came and
Output:
commissary, quartermaster in the Roman army
I used many additional REGEXTRACT formulas to automate the extraction of relevant strings, such as: all words inside parentheses; words written with Greek characters; and anything after the word 'Lat. " (=Latin), until hitting a period ('.').
Match and capture any characters from the beginning of the string until the last underscore:
=REGEXEXTRACT(A1, "^(.*)_.*")
Example input:
XXX-YYY-ZZZ_01.jpg
Output:
XXX-YYY-ZZZ
Using the REGEXMATCH Function to Identify Cells that Contain a Letter, Word, or Phrase
Much better than Excel’s ISNUMBER(SEARCH(x), which requires doing that for each string searched for. See here:
=REGEXMATCH(A1, "good|wonderful|amazing")
REGEXREPLACE
Much better than Excel’s SUBSTITUTE, which requires doing that for each string substituting.
Currency Conversion in Google Sheets
I use it for converting shekels to dollars, in a sheet that I use to track my net worth, for converting the values of my Israeli bank accounts, pension, and other investments.
Formula, where the shekels input is in cell A1 (ILS = shekels, USD = dollars):
=GOOGLEFINANCE( "CURRENCY:ILSUSD" ) * A1
Convert to chip - link to another Google Sheet or Doc
I manage my to-do list, and blogpost queue, using Google sheets. I link to each sheet/doc, and convert to chip, which automatically pulls the title.
Screenshot:
Open multiple URLs at once
Native feature in Google sheets!
Screenshot:
ChatGPT
Convert ChatGPT Headers
When copying, ChatGPT output is formatted in Markdown.
Markdown - Wikipedia: “Markdown is a lightweight markup language for creating formatted text using a plain-text editor.”
Screenshot of examples, from that entry:
To convert it, I discovered the following Google docs plugin: “Markdown To Docs (GdocifyMd)”. It works well.
Convert ChatGPT Tables
Similarly, ChatGPT’s tables are formatted by default in Markdown style, when copying. Meaning, separated by pipe symbols (‘|’).
Example:
Screenshot:
When copying:
| ID | Name | Value |
|----|----------|-------|
| 1 | Alice | 100 |
| 2 | Bob | 200 |
| 3 | Charlie | 300 |
To convert, I use Google Sheets’ SPLIT function (see upcoming piece where I discuss this function a bit more), and split by pipe symbol (‘|’).
outstanding. my life hack is just to subscribe to your blog!