Learn to use RegEx in Google Sheets in 10 minutes
Вставка
- Опубліковано 23 лип 2024
- Tutorial for using Regular Expressions in Google Sheets (extract, replace, and match)
❤ Subscribe: bit.ly/SubscribeDPT
Learn How To Build Regular Expressions
► RegEx Essentials Part #1: • RegEx Essentials - Wha...
► RegEx Essentials Part #2: • Regex Essentials - Adv...
In this tutorial, I will show you how to use Regular Expressions in Google Sheets. including the use of the REGEXEXTRACT, REGEXREPLACE, and REGEXMATCH functions.
Regular Expressions are a very useful tool for easily manipulating strings and testing input. It is one of the most useful skills and one of the toughest to master. I hope this video will get you familiar with the common usages of Regex so you can start using it everywhere.
📺 In this video:
0:00 - Intro
0:34 - Extract text with RegEx
1:36 - Extracting with capture groups
2:45 - Replacing text with RegEx
3:52 - Text Normalization with RegEx
5:03 - Validating input with RegEx
6:48 - Using the case-insenstive flag
9:24 - Conclusion
🧮 Source sheet (feel free to make a copy)
docs.google.com/spreadsheets/...
🔢 More RegEx Essentials Tutorials - Learn to code with Regular Expressions!
► • Regex Essentials (Tuto...
💻 More tutorials
► • Tutorials
🙋♂️ Find me on other channels
Discord 🗨️ bit.ly/dpt-discord
Twitter ✍ / denvercoder1
Github 👨💻 github.com/DenverCoder1
Sponsor 💞 github.com/sponsors/DenverCoder1
One-time donation ☕ ko-fi.com/jlawrence
💖 SPONSORS 💖
Get your username or a link to your channel here by sponsoring on Github
▶ github.com/sponsors/DenverCoder1
🎁 GET FREE STUFF WHILE SUPPORTING MY TUTORIALS
► bit.ly/jlawrencepromos - Навчання та стиль
Thank you for this video, you are a very clear presenter.
Thank you. Very useful!
Great video Jonah! You helped me a lot.
Mind blowing! Programming just got a hell of a lot simpler.
This video is great. Love the real world examples. Keep up the good work.
Thanks, Walter! I appreciate the support!
That was butter smooth 👍👍
Wow, thank you. fantastic and super quick. Subbed
Thanks so much!
Thank you! Helped me a lot. By the way: nice music at the end.
Thanks so much, Sven!
Hey Jonah, thanks for the video, I'm wondering how I would extract alpha(s) and the spaces and excluding the digits.
Woooww...great tricks..amazing. 👍👍
Thanks!
This is a world of excel/sheets that I didn't know existed
Hey Jonah, great tutorial! I was wondering if you could help me.. I'm trying to extract values after the specific word 'rawValue'. The values that follow that word come in three different variations:
A word: rawValue=\"—
A large positive number: rawValue=\"280243000\"
A large negative \"-280243000\"
A small number: rawValue=\"0\"
=(REGEXEXTRACT(A60,".*rawValue=(.*)")), which only extracts the first value. How would I be able to extract them all? The amount of cases in a cell varies as well.
Thank you in advance!
Google Sheets only allows a single capturing group to return 1 match, so you'll need multiple groups.
You can use REGEXREPLACE to insert capturing groups and then extract all of the groups that are captured.
Here's an idea to get you started:
=REGEXEXTRACT(A60, REGEXREPLACE(A60, "(rawValue=)(\S+)", "$1($2)"))
I used this regular expression in Excel previously
^(GP\:){0,1}9716[123]
to find any of these values
"97161"
"97162"
"97163"
"GP:97161"
"GP:97162"
"GP:97163"
However, this expression is not working in Sheets.
Do you have any suggestions to correct the expression? Or other ways to search for those values in a formula?
That formula should work for most cases. If you're doing an extract, the parentheses around the GP: part make it a capturing group which will make it so just the GP: will be extracted.
Putting ?: at the beginning of a group makes it non-capturing and more efficient if you don't intend on using the GP: value by itself.
In short, this is how I'd simplify it if GP: does not need to be captured:
^(?:GP:)?9716[123]")
If you want to detect it *within* a string and not just the beginning, you'll want no anchor:
(?:GP:)?9716[123]")
Hello can you help me fot my project? I want to extract the first 2 words of the file name. And if the first word is numbers. I want to extract the next 2 words to it. Pls help. I can pay thru paypal.
Please share this sheet for practice
Feel free to make a copy and use it as you wish
docs.google.com/spreadsheets/d/1LkhqhGgiXDb4x1ouZeJOUwKlVd3LJpJDDMgBe12RQcU/edit?usp=sharing
@@DevProTips thanks
I showed this to my boyfriend and he said "I love how Google Sheets turns you on".
Didn't get anything. It is not for beginners