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: 

"Jastrow's Greek Gems: Or, How I Extracted and Processed All 1000+ Greek loanwords Defined in Jastrow's Dictionary - Pt.1": 

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 (‘|’).