Are you stuck on a tricky data cleaning project?

Do you need to extract web addresses or dates from unstructured text?

Trying to figure out how to get rid of special characters stuck inside words?

Got a list of credit card numbers you need to format?

This week we cover a new family of functions in Excel that makes it all possible. Simple even, with the right kind of help.

Also in focus today:

• Tables in Google Sheets
• More matrix layouts in Power BI
• AI Recall will know everything about your computer 👀

If you were forwarded this message, you can get the free weekly email here.

🎬 New REGEX functions in Excel: extract, clean and format data like a Pro

Exciting news from the Excel world. We're getting new functions! 🥳

They're currently in Microsoft 365 Insiders Beta version. But they're worth the wait.

Want to grab all email addresses embedded in text?

REGEXEXTRACT function makes it easy:

If you've never heard of Regular Expressions, or REGEX, they are sequences of characters that help you find patterns in text strings. Once you find a match, you can extract it, replace it, clean and format.

Sounds super geeky and complicated? Don't dismiss it just yet.

There are many business cases where REGEX is exactly what you need to achieve your objective.

And in this AI era, you can outsource generating the right regex pattern for each scenario to Copilot or ChatGPT.

Just plug it into your formula and enjoy the results.

Let me walk you through some practical scenarios where the new REGEXEXTRACT and REGEXREPLACE make a major difference.

​

From the video, you'll learn:

• how to write effective AI prompts that will return the exact REGEX pattern you need,
• how to use the optional arguments in the new functions,
• how to combine them with other functions for flexible results.

👉 Here's the link to the complete file, including a cheat sheet that explains common Regex patterns.

BTW, there's a third REGEX function - REGEXTEST.

REGEXTEST returns TRUE or FALSE if a specified pattern is found in the text. It could be useful for validations.

Funny note: When I asked Excel Copilot if it's familiar with the new Excel Regex functions it said:

No mention of REGEXTTEST - instead REGEXMATCH!! Which is what most of us MVPs wished Microsoft had named this function.

Anyhow, remember, these functions are currently in preview. Microsoft might still update them based on insiders' feedback.

And for extra good news - soon XLOOKUP and XMATCH will support REGEX as well, making them even more powerful!

I'll keep you updated!

​

🤓 Geeky News

🌈 Google Sheets is getting tables!

Google Sheets has had REGEX functions for a while now. What it was missing was the ability to convert ranges into official table objects.

The wait is over. Google Sheets is finally getting tables.

Simply select your range, go to Format > Convert to table.

Many benefits should be familiar if you've used tables in Excel. With tables:

• The range automatically expands with new data.
• You get automatic filters.
• You can also use table references in formulas, that is, refer to a column by its header instead of a cell range.

There are also a few features unique (at least for now) to Google Sheets. One is the ability to easily assign column types, like date, dropdown, checkbox, etc. You can also group rows by a selected column.

First impressions? The table formatting is not as loud and busy as Excel's.

I might even leave it be instead of immediately removing the style. 😉

I also appreciate the ease of creating dropdowns and inserting checkboxes from the column header.

The feature is rolling out gradually, but you should see it in your Sheets by the end of June.

📊 New Matrix layouts and other Power BI May updates

Matrix in Power BI is like a pivot table in Excel. Now even more so.

​The May release of Power BI Desktop brought enhancements to matrix formatting.

You can now change the layout, just like for pivot tables in Excel. Choose between compact (the default), outline and tabular.

Previously, you could switch between compact and outline layouts using the Stepped toggle. The new tabular layout is a hybrid combining elements of a matrix and a table.

You also get the ability to repeat row headers and to add a blank row between row header groups. The goal of these changes is to improve the readability of matrices.

💻 Microsoft Recall will help you find anything on your computer

Microsoft has introduced a new AI-powered search feature in Windows 11, called Recall.

Recall can remember everything you do on your PC and lets you search using natural language.

Say, you were working on a document and can't for the life of you remember the file name. Try asking for "project estimates I worked on last month".

Looking for a picture someone sent you? Describe what you remember seeing in the photo.

The feature stores data locally and encrypts it using BitLocker. Users can manage which activities are recorded and delete stored snapshots through Settings.

While Recall doesn't capture sensitive information like passwords, it still poses privacy concerns.

You'll have to weigh the convenience against the risk of having all your digital activities monitored.

If Recall sounds exactly like what you need, you'll need to get a new computer.

Recall requires a Neural Processing Unit (NPU) to work efficiently. NPUs are special processors optimized for machine learning and artificial intelligence. Those AI-enabled laptops are only now hitting the market.

If Recall sounds a tad too intrusive, you can breathe a sigh of relief. Your current device most likely doesn't support it.

​

👏 Power Stories

Congrats to Aaqib on completing the Unlock Excel VBA and Excel Macros course! 🎉

It's great to know that you're applying your new VBA skills to improve efficiency and save time. Testimonies like this make my day.

Got a success story about using your XelPlus skills? I’d love to hear it! Hit reply to share the details, and inspire other students 😇

See you next week,

Leila

Want more?

▶️ Subscribe on YouTube​

🖇️ Follow us on LinkedIn​

🥇 Join 400,000+ students in our courses​

📣 Want to sponsor Between the Sheets? Get in touch here.

This newsletter contains affiliate links, which give us a small commission on any purchase made at no cost to you. This helps us run Between the Sheets and bring you updates like this. Thank you for your support!