How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Wednesday

Regex to Get All Words after Nth Word in a Sentence in Google Sheets

Posted by   on

We can use Regex to get all words after the nth word in a sentence in Google Sheets.


For that, there is a built-in function called REGEXREPLACE. Another option is the QUERY, SPLIT, and SUBSTITUTE combo. We will try that too.


Actually, I was about to write another tutorial to explain a formula that I have offered to one of my readers via comment reply. It was about comma-separated values.


As part of that formula, I wanted to extract all the words after the first word.


Since that’s a complex formula, I thought to write this tutorial to explain that specific part.


As mentioned above, to get all the words after the nth word in a sentence, we will use REGEXREPLACE instead of another related function called REGEXEXTRACT.


The reason is like this.


We will replace the first n words and non-word characters, such as a space with blanks.


The rest of the sentence part will be the result we wish to get as the formula output.


Let’s quickly try our non-regex formula to get all words after the nth word in a sentence. The regex formula follows.


Without Using RE2 Regular Expression


To test, we may use the following dummy text in cell A1.



Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus posuere efficitur sapien, eu euismod lacus vulputate vitae.



Non-Regex Formula that Substitutes a Delimiter

=Query(split(SUBSTITUTE(A1," ","🐠",1),"🐠"),"Select Col2")

The above formula extracts all the words after the first word from the dummy text in cell A1.


Replace the number 1 in the formula with the number 5 to get all the words after the fifth word in the sentence.


Formula Explanation:


We have used the SUBSTITUTE formula to replace the first occurrence of the space character with the fish character.


We then split the sentence into two based on the said delimiter.


That will create two-column data. The QUERY is to return the data from the second column, and that is what we want.


Does the above non-regex formula that returns all words after the nth word in a sentence will work in an array?


Yes, it will! Here is an example.


=ArrayFormula(Query(split(SUBSTITUTE(A1:A100," ","🐠",1),"🐠"),"Select Col2"))

In the above, you can see a cell range in use (A1:A100) instead of the cell reference (A1).


In addition to that, as you may already know, I have included the ARRAYFORMULA function to expand the result.


Note:- Use TRIM(A1:A1000) or TRIM(A1) if you doubt the words in your sentence are separated by multiple spaces intentionally or due to typo. In the following REGEX, it’s not required.


That being said, let’s go to the regular expression based formula.


Regex to Get All Words After Nth Word in a Sentence


We can use the below regular expression for the same in the REGEXREPLACE function.


Regular Expression and Formula Options


Expression:


^(\w*\W*)2

Elements:


^ – Asserts position at the beginning of text or line.


\w – Matches word characters (underscore and alphanumeric characters [0-9A-Za-z_]).


* – Capture preceding token between 0 and unlimited times.


\W – Matches ‘not word’ characters (not underscore and alphanumeric characters [^0-9A-Za-z_]).


n – Matches the preceding token exactly n times.


Note:- Please refer this GitHub page for RE2 syntax reference.


The above is the regex regular expression that we can use to get all the words after the nth word in a sentence in Google Sheets.


How to use the above regular expression in a Google Sheets formula?


Here you go!


=REGEXREPLACE(A1,"^(\w*\W*)3","")

Array Formula to Get All Words after the Nth Word in a Sentence in Google Sheets


The above formula will support cell range that means capable of returning an array result.


Here is an example for you to understand the array use of the above regular expression in Google Sheets.


=ArrayFormula(REGEXREPLACE(A1:A,"^(\w*\W*)2",""))

Regex Array Formula to Get All Words After the Nth Word

In the list of names from column A, the above REGEXREPLACE array formula returns the last name.


It replaces the first two words, i.e., the serial number and first name, with blank.


That’s all. Thanks for the stay. Enjoy!


Related Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!