Hi Reader,

Welcome to the Google Sheets Tips newsletter #240!

➜ News

Two big announcements from the Google Sheets team recently:

I.
11 new functions!

Yes, you read that right.

After the function bonanza last summer (lambda + named functions), we're now spoilt with another batch of incredibly useful functions.

This new batch includes LET, for naming value expressions, as well as a slew of array manipulation functions. Great stuff!

​Read more about 11 new functions here​

(I'd better get to work on some new tutorials!)

II.
​Connected Sheets — which lets you analyze big data inside of Google Sheets — now connects Sheets to the Looker platform, in addition to BigQuery.

​Read more about Looker data in Connected Sheets​

_______

➜ Google Sheets Tip #240:

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

My wife and I both work from home so we use a combination of shouting across the house and Google Chat to communicate.

Last week, I wanted to share a story in Google Chat about a "bootstrapped-founder-joining-a-startup-again" except that I was too lazy to do all that hyphenation myself.

Naturally, I turned to Google Sheets and the SUBSTITUTE function to do it for me:

This relatively simple formula searches for spaces and replaces them with hyphens.

It turns "bootstrapped founder joining a startup again" into "bootstrapped-founder-joining-a-startup-again".

But, wouldn't it be nice to have a native HYPHENATE function I thought? 🤔

Something like this:

That's much easier to use than the substitute formula above.

Thankfully, you can create one!

You can use Named Functions — the new function paradigm introduced last year — to do this.

Go to the menu: Data > Named functions

At the bottom of the sidebar, click: Add new function

Give the new function a name (HYPHENATE) and a description. Then enter the SUBSTITUTE formula above in the formula definition.

Change the argument A1 to a placeholder "text".

Then, click Next to continue.

On the next page, you can see the function preview and add additional details for the autocomplete help pane:

Click Create to create the new named function.

Now, you can simply type HYPHENATE into your Google Sheet to use this new function to perform the operation on some text.

Sharing Named Functions

Named functions are really powerful because you can share them with other Sheets.

For example, suppose you want to use the HYPHENATE in a different Google Sheet file.

In that other Sheet, go to the menu: Data > Named functions

This time, at the bottom of the sidebar, click: Import function

Select the other Sheet, the one where you defined the original HYPHENATE named function.

Select this Sheet and when prompted, check the box for the HYPHENATE function to import it into the new Sheet.

Genius!

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. My good friend Kevin Markham, founder of Data School, recently started a weekly tips newsletter for data scientists.

He publishes one new data science tip every Tuesday morning.

​Find the first one here and sign up for yourself​