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.
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
Great video Jonah! You helped me a lot.
Mind blowing! Programming just got a hell of a lot simpler.
Thank you for this video, you are a very clear presenter.
Thank you! Helped me a lot. By the way: nice music at the end.
Thanks so much, Sven!
Thank you. Very useful!
This video is great. Love the real world examples. Keep up the good work.
Thanks, Walter! I appreciate the support!
Hey Jonah, thanks for the video, I'm wondering how I would extract alpha(s) and the spaces and excluding the digits.
That was butter smooth 👍👍
Wow, thank you. fantastic and super quick. Subbed
Thanks so much!
how did i not know this existedddd
Woooww...great tricks..amazing. 👍👍
Thanks!
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)"))
This is a world of excel/sheets that I didn't know existed
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