I have been trying to figure out how to do this for ages. My thanks for this great video. It will save me so much time. Very well and clearly explained. Also, you don't rush through it like most people on here. very helpful. :)
How to extract word that is before UnderScore Ex:-" sample1_lab1_part1" i want to extract "sample1 " Please help me with this... I tried and got output as "sample_" , but i want Just sample
Your tutorials are great but it will be helpful if you also provide the file that you are using so that we can practice with you. Thanks for the tutorial.
Thank you for the video! Question...how can you extract multiple words from a cell. For instance, If I had a cell containing "Company X Information Technology and Data Services" and I'd like to extract only the following words: "X", "Technology", "Data Services" - how can I do this? Thanks in advance!
That's a very specific pattern! You're basically saying that "Company _ Information _ and _" is fixed, it will always be there. If that's really the case, then the pattern would be.. "Company (\w+) Information (\w+) and (.+)$" .. this would yield your output in 3 adjacent cells, beginning with the cell containing the formula. More versatile would be .. "Company (.+) Information (.+) and (.+)$" .. which would handle multi-word companies (eg: instead of just a single word company "X", it would handle "Dell Computers". Or "International Business Machines" etc)
How do I use regex to extract 2 pieces of info , for eg. if I have to tracking numbers how to I extract both of them if they both start with same “Tracking #”.
Is there any formula that can split/divide data of one row in to multiple rows, keeping the text data same and divide the values depending on some percentages?
Hello. I have some lines but put into one cell. I would like to add a "-" at the beginning of every line. What should I do? I try \w but it only works at the first line.
Hi, I have one issue with REGEXEXTRACT. I want to match and extract the exact word. If I use the below formula, it will display the result “Sing,” but I want it to display “NA.” Because the source cell i.e A1 cell does not have the “Sing” word but it has the word "Singapore". =regexextract(A1,”(?i)Sing”) So, how can we extract the exact match instead of the partial match? Thanks & Regards, Vineet
how do you pull the characters between brackets [ ] ? For example, when I get Google Form data, the survey questions have the names of different options I gave in brackets like [Option 1] and [Option 2]
Please Help me with the formula I have a cell (3.50%) or (4.50%) I need the formula to convert into (3.50) or (4.50) just want to remove "%". thank you
Hi! These videos have been a huge help but I recently ran into a small problem. For example if I had a column containing things like "product-1-description x1, product-2-description x2, so on so forth," and my goal is to convert them to "product 1 x1, product 2 x2, etc.," how would I do that. Im new to regex but I can successfully find/replace "product-1-description x1" into "product 1 x1." But everything after that gets deleted. Is there a way to insert a regular expression for that comma so that the following terms get replaced as well?
Great explanation! However, when I tried to extract th number from a text it didn't work properly for all the cells. Look at that: tube cleaning (mechanical 50%) 50 tube cleaning (mechanical 100%) 10 gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvececev 50%) 50 gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvece 100%) 10 gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvececev 40%) 40 gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvececev) tube cleaning (mechanical 100%) 10 tube cleaning (mechanical 50%) 50 tube cleaning (operator 60%) 60 tube cleaning (welder 100%) 10 It isn't extracting 100 number. It is bringing 10. That's how I used the function: =IFNA(REGEXEXTRACT(A1;"\w[0-5]");"") May you help me out, please?
URGENT PLEASE: how can i do to add a word and a slash like this (/hi) to a found regex? i need this "/hi" before this regex \b\w{5,5}\b . if someone puts a name with 5 characters in it it must find that 5 characters name witch it does but it has also to add /hi infront of it as a result when it finds it
@@ExcelGoogleSheets I'm trying to extract the last digits of a link where that handle is equal to a 'Product ID' number. Since the links have several "/", it's difficult to extract just the very end of the string followed by the last "/". I.e. the link is website/product-page/12345 but I only need "12345".
I don't know why, but it doesn't work! even the 1st example formula, the result is "Formula parse error." I have received this message even with a copy-paste in a google sheet of the goole guide support.google.com/docs/answer/3098244?hl=it, and I'm receiving anyway the same result: "Formula parse error." Does anyone have a suggestion?
Character classes . any character except newline \w \d \s word, digit, whitespace \W \D \S not word, digit, whitespace [abc] any of a, b, or c [^abc] not a, b, or c [a-g] character between a & g Anchors ^abc$ start / end of the string \b \B word, not-word boundary Escaped characters \. \* \\ escaped special characters \t
I have been trying to figure out how to do this for ages. My thanks for this great video. It will save me so much time. Very well and clearly explained. Also, you don't rush through it like most people on here. very helpful. :)
Great beginner video to RE2 syntax. One thing, can you add a link to a sample copy of your sheet used in the explanation?
講得非常清楚，感謝～
um... legit magic. Appreciated
thank you, very easy to follow your tutorials
btw I think you posted REGEXEXTRACT videos in reverse order ?
Excellent tutorial, thanks for sharing
Thank you so much this is extreamly helpful!
great video thanks for sharing
Best tutorial, happy to following you...it's help me a lot, thanks
THankyou very much for your video, very very helpfulllll 😭
Great as always!!
Double Thumbs UPPPPPPPPP
How to extract word that is before UnderScore
Ex:-" sample1_lab1_part1"
i want to extract "sample1 "
Please help me with this... I tried and got output as "sample_" , but i want Just sample
Your tutorials are great but it will be helpful if you also provide the file that you are using so that we can practice with you. Thanks for the tutorial.
Great video, thank you!
❤❤❤
Thank you! :-)
Thank you for the video! Question...how can you extract multiple words from a cell. For instance, If I had a cell containing "Company X Information Technology and Data Services" and I'd like to extract only the following words: "X", "Technology", "Data Services" - how can I do this? Thanks in advance!
That's a very specific pattern! You're basically saying that "Company _ Information _ and _" is fixed, it will always be there. If that's really the case, then the pattern would be..
"Company (\w+) Information (\w+) and (.+)$"
.. this would yield your output in 3 adjacent cells, beginning with the cell containing the formula.
More versatile would be ..
"Company (.+) Information (.+) and (.+)$"
.. which would handle multi-word companies (eg: instead of just a single word company "X", it would handle "Dell Computers". Or "International Business Machines" etc)
How do I use regex to extract 2 pieces of info , for eg. if I have to tracking numbers how to I extract both of them if they both start with same “Tracking #”.
How can we extract the last 2 words from a string? thanks in advance - great tutorial!!
(\w+)\W+(\w+)$
Is there any formula that can split/divide data of one row in to multiple rows, keeping the text data same and divide the values depending on some percentages?
SPLIT function?
How can I use these functions in Script editor? I am having trouble in completing a task. Any help will be appreciated.
Hello. I have some lines but put into one cell. I would like to add a "-" at the beginning of every line. What should I do? I try \w but it only works at the first line.
="-"&A1
How do you enable that example guide above your function when adding regular expressions.
Click on the question mark.
Hi,
I have one issue with REGEXEXTRACT. I want to match and extract the exact word. If I use the below formula, it will display the result “Sing,” but I want it to display “NA.” Because the source cell i.e A1 cell does not have the “Sing” word but it has the word "Singapore".
=regexextract(A1,”(?i)Sing”)
So, how can we extract the exact match instead of the partial match?
Thanks & Regards,
Vineet
Incomplete question. Bring several examples of what should and shouldn't match,
how do you pull the characters between brackets [ ] ? For example, when I get Google Form data, the survey questions have the names of different options I gave in brackets like [Option 1] and [Option 2]
i'd use a formula with textbefore and textafter (but i'm a noob)
Please Help me with the formula I have a cell (3.50%) or (4.50%) I need the formula to convert into (3.50) or (4.50) just want to remove "%". thank you
Hi! These videos have been a huge help but I recently ran into a small problem. For example if I had a column containing things like "product-1-description x1, product-2-description x2, so on so forth," and my goal is to convert them to "product 1 x1, product 2 x2, etc.," how would I do that. Im new to regex but I can successfully find/replace "product-1-description x1" into "product 1 x1." But everything after that gets deleted. Is there a way to insert a regular expression for that comma so that the following terms get replaced as well?
Hi,
Did you find the answer to this problem?
If you did, can you share? I am trying to work around a similar problem
Sr where are you upload more new things
Great explanation! However, when I tried to extract th number from a text it didn't work properly for all the cells. Look at that:
tube cleaning (mechanical 50%) 50
tube cleaning (mechanical 100%) 10
gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvececev 50%) 50
gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvece 100%) 10
gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvececev 40%) 40
gdwdtge eyeeeetetec (gecgedetde eedgedegded-cgccegcecvececev)
tube cleaning (mechanical 100%) 10
tube cleaning (mechanical 50%) 50
tube cleaning (operator 60%) 60
tube cleaning (welder 100%) 10
It isn't extracting 100 number. It is bringing 10.
That's how I used the function: =IFNA(REGEXEXTRACT(A1;"\w[0-5]");"")
May you help me out, please?
match and replace to columns together google sheets text to columns
URGENT PLEASE: how can i do to add a word and a slash like this (/hi) to a found regex? i need this "/hi" before this regex \b\w{5,5}\b . if someone puts a name with 5 characters in it it must find that 5 characters name witch it does but it has also to add /hi infront of it as a result when it finds it
="/hi"®EXEXTRACT()
🙏🏻
Can you tell me how to remove any characters before colon
this should work
=REGEXREPLACE(A1,".*:","")
I am not able to use "REGEX" function in EXCEL 365 as well as Google Sheets!
Excel doesn't have REGEX functions, at least for now.
kindly help me for the practice file. please🙏🙏
Is there a way to extract text from the end of a string? I'm looking to extract the handle of a URL.
Please provide an example
@@ExcelGoogleSheets I'm trying to extract the last digits of a link where that handle is equal to a 'Product ID' number. Since the links have several "/", it's difficult to extract just the very end of the string followed by the last "/". I.e. the link is website/product-page/12345 but I only need "12345".
@@naomipena6318 REGEXEXTRACT(A1,"\d+$")
@@ExcelGoogleSheets Thank you!! It worked.
great!
How to run the REGEXEXTRACT function on a PC program Excel?
You'll need a custom VBA function. Otherwise it doesn't exist in Excel.
how to find 3rd word with regex
\w+\s+\w+\s+(\w+)
Please sort your playlist ;)
I don't know why, but it doesn't work! even the 1st example formula, the result is "Formula parse error." I have received this message even with a copy-paste in a google sheet of the goole guide support.google.com/docs/answer/3098244?hl=it, and I'm receiving anyway the same result: "Formula parse error." Does anyone have a suggestion?
th-cam.com/video/ljvB2arCsNQ/w-d-xo.html
@@ExcelGoogleSheets Wow! god job! :D really thankful! sharp, fast, efficient!
Teacher... are you Retired now?
Character classes
. any character except newline
\w \d \s word, digit, whitespace
\W \D \S not word, digit, whitespace
[abc] any of a, b, or c
[^abc] not a, b, or c
[a-g] character between a & g
Anchors
^abc$ start / end of the string
\b \B word, not-word boundary
Escaped characters
\. \* \\ escaped special characters
\t
tab, linefeed, carriage return
\u00A9 unicode escaped ©
Groups & Lookaround
(abc) capture group
\1 backreference to group #1
(?:abc) non-capturing group
(?=abc) positive lookahead
(?!abc) negative lookahead
Quantifiers & Alternation
a* a+ a? 0 or more, 1 or more, 0 or 1
a{5} a{2,} exactly five, two or more
a{1,3} between one & three
a+? a{2,}? match as few as possible
ab|cd match ab or cd