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

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:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.