The easiest way to match the nth word in a line in a cell in Google Sheets is by using the REGEXMATCH function. What about extracting the nth word?

To extract the nth word from a sentence/line in Google Sheets, we can depend on the REGEXEXTRACT function.

Further, in both the cases (extract/match), we can use a SPLIT + INDEX combo formula.

First of all, let’s learn how to extract the nth word from a sentence or line in a cell in Google Sheets. Then we can move to match the nth word in Google Sheets, as in the match also we need to extract the nth word first.

Extract Nth Word from a Sentence in Google Sheets

In my sheet, cell A2 contains the idiom “Let the cat out of the bag”. I’ll show you how to extract each word from this line in Google Sheets.

As a side note, I have chosen this idiom without any rhyme or reason. My intention is to just to make you understand how to extract the nth word from a line/sentence in Google Sheets. You can test my formula with your real-life data.

Using SPLIT + INDEX Combo (Non-Array Formula)

First I am giving you the generic SPLIT + INDEX combo formula to extract the nth word from the said line (idiom) in Google Sheets.

Syntax:

=index(split(line_to_extract," "),1,n)

Just change line_to_extract with cell A2 and ‘n’ with the word number in the order.

For example, “cat” is the third word in the sentence/idiom in cell A2. So to extract the word “cat”, use the below formula.

=index(split(A2," "),1,3)

Formula Explanation

The SPLIT formula splits the words in the sentence/line into columns. The space (delimiter) in the sentence enables us to split the words.

split(A2," ")

Split Result in Columns:

There are 7 words, so the output will be in 7 columns.

We want to extract the 3rd word from these. The INDEX formula offsets three columns and returns the word “cat”.

We can use the above result (formula itself) to match the nth (here 3rd) word in a line (here idiom) in Google Sheets. But before going to that, here is one more formula to extract.

Using REGEXEXTRACT (Non-Array Formula)

Here is the REGEXEXTRACT equivalent for extracting the 3rd word from the line/sentence.

=REGEXEXTRACT(A2&" ","^(\w+\s){3}")

The number 3 within the Curly Braces (also called Curly Brackets) controls the nth position of the words. That means to extract the 2nd word, use the number {2} instead.

The above are the two different methods to extract nth word from a line/sentence in Google Sheets.

Array Formula to Extract Nth Word in Google Sheets

We can use the REGEXEXTRACT function to extract nth word from a range (array). But, the SPLIT + INDEX won’t work in an array. Instead, we can replace the INDEX with QUERY.

Formula 1:

The following formula in Google Sheets would extract all the 2nd words from the range A2:A since we have used Col2 in the ‘Select’ clause in the QUERY formula.

=ArrayFormula(Query(split(A2:A," "),"Select Col2"))

Use Col3 to extract all the third words.

Here is the above array formula equivalent using REGEXEXTRACT.

Formula 2:

=ArrayFormula(if(A2:A="",,REGEXEXTRACT(A2:A&" ","^(\w+\s){2}")))

That’s all about how to extract the nth word from a line in a cell (also in an array) in Google Sheets.

REGEXMATCH to Match Nth Word in a Sentence in Google Sheets

Here we can use either of the REGEXEXTRACT or SPLIT + INDEX formulas within REGEXMATCH to match the nth word in a line in Google Sheets.

I am skipping the SPLIT + QUERY because I’m not going to explain array formula usage this time as it may not come in use in real-life.

If you are looking for an array formula to match the nth word in Google Sheets, please let me know in the comments. I’ll try to help you out. Now let’s come back to the example.

We have the earlier said idiom in cell A2. I just want to test whether the third word is “cat” in that idiom.

Steps:

Extract the third-word using REGEXEXTRACT or SPLIT + INDEX combo as explained above and TRIM it. I am using the former one.

=trim(REGEXEXTRACT(A2&" ","^(\w+\s){3}"))

Then REGEXMATCH the word “cat” with the extracted word, I mean the extract formula itself.

=REGEXMATCH("cat",trim(REGEXEXTRACT(A2&" ","^(\w+\s){3}")))

The formula would return TRUE if it matches, else FALSE.

Resources

• Regex to Replace the Last Occurrence of a Character in Google Sheets.
• Match Two Columns that Contain Values Not in Any Order Using Regex.
• Extract Username from Email Address Using Regex in Google Sheets.
• How to Extract Multiple Words Using Regexextract in Google Sheets.
• How to Extract Date From Time Stamp in Google Sheets.
• Extract All Numbers from Text and SUM It in Google Sheets.
• Google Sheets: Compare Two Lists and Extract the Differences.
• Extract Vowels and Consonants Separately in Google Sheets.
• Extract Unique Values from a Comma Separated List in Google Sheets.
• Regexmatch Dates in Google Sheets – Single/Multiple Match.
• Extract Numbers Within Square Brackets in Each Row in Google Sheets.
• How to Offset Match Using Query in Google Sheets.