Hi Reader,

Welcome to the Google Sheets Tips newsletter #264, your Monday morning espresso, in spreadsheet form!

Today's tip is all about the powerful REGEX formulas in Google Sheets.

They are tricky to use but reward your effort in usefulness.

I encourage you to work through the example below and, even if you never write another REGEX formula, at least you'll have this one in your toolbox.

But... if you're keen to learn more, check out the special offer on my REGEX cookbook course at the end of this email.

A big thank you to this week's sponsors who help keep this newsletter free for you to read:

SUPERMETRICS A marketer's ultimate cheat code for spreadsheets! Want to automate your marketing reporting AND analysis in spreadsheets? The Supermetrics x ChatGPT integration is how. Get data from 100+ platforms automatically to Google Sheets and let AI generate a summary of your performance. Working with data has never been easier. Start your 14-day free trial today!

COEFFICIENT Miro, Uber, & Spotify’s Secret to Automating Google Sheets Data & Reports Never manually export data again – join 150K+ GTM pros to save countless hours. Sync live data into Sheets from your CRM, SaaS tools, BI platform, and database. Schedule refreshes, blend data, and trigger notifications – all within your sheet. Get Started Free with Templates

➜ News

A few minor updates from the past few weeks:

I.
​When pasting email addresses or Drive file URLs into Sheets, you can easily convert them to smart chips with the Tab key:

Source: Workspace Updates Weekly Recap - July 7, 2023​

II.
​We now have a quick and easy way to insert emojis into our Google Sheets.

Type "@" > "Emoji" into a cell or use the menu: Insert > Emoji

Perfect for highlighting good results to the boss! 🎉

Source: Workspace Updates Weekly Recap - July 14, 2023​

_______

➜ Google Sheets Tip #264: How to extract numbers with REGEXEXTRACT

Today we're exploring regular expressions, also known as REGEX.

Don't worry though, we'll keep it simple! But hopefully, this example will illustrate the power of REGEX.

Let's start with this dataset:

Now suppose we want to extract the apartment values into a new column.

We'll use the REGEXEXTRACT function to do this.

Step 1

Start with this simple regular expression:

Output: 8

The "[0-9]" is the regular expression that means extract a single number from 0 to 9 in the input string.

Hmm, if you use this it will extract the first "8" that is part of the address "8th Street".

Step 2

We need to force it to find the $ amount. So we modify the regular expression to say find the $ first, then the numbers that follow it. We do this by adding a dollar to the regular expression.

Output: $3

Notice that we need to add a backslash before the $ sign. The $ sign has a special meaning in REGEX so we need to use this backslash (called an escape character) if we want to actually search for the $ itself.

Step 3

The formula still only extracts the first digit, so let's fix that by adding a "+" after the square brackets:

Output: $328

Step 4

The values in the original data have thousand separators (commas) so we need to tell the REGEX formula to extract those too!

Add the comma inside the square brackets after the 9, like so:

Output: $328,000

Step 5

The output of the REGEXEXTRACT function is a text value, so even though our output looks good, Google Sheets doesn't think of it as a number yet.

Thankfully this is easily remedied by simply multiplying by 1:

Output: 328000

Now that the output is a true number, you can add the formatting back via the standard number formatting options.

This is just the tip of the iceberg with REGEX formulas. They're incredibly powerful tools when you're working with data.

Bonus AI Tip

Try using AI tools like ChatGPT or Google Bard to help you understand and create REGEX formulas!

These tools are not perfect but even if they don't get the full answer they'll get you pretty close. And they're improving all the time.

Learn more about the REGEX formulas in Google Sheets. Get 25% off my REGEX course.

Want to learn how to use the REGEX formulas in Sheets?

Get 25% off my REGEX Formula Cookbook course with the coupon SUMMER25 at checkout, or use this link:

​REGEX Formula Cookbook Summer Sale​

Offer valid until the end of July 2023 and only applies to the REGEX course.

_______

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

Have a great week!

Cheers,
Ben

P.S. How big? Modern cruise ships are GIGANTIC​