How to Extract the last N Words from Text String in Excel

Excel is a tool for storing and managing a large amount of data. Data is entered in a rectangular block, which we called a cell. This tool can be used to perform different tasks like creating graphs and analyzing trends, to get insights from the data. It is primarily used for financial data analysis by accounting professionals. Let’s learn how to extract the last N-word in Excel. 

For example, we have a string “Welcome to GeeksforGeeks platform” and we need to extract the last 2 words from the string, then the output should be “GeeksforGeeks Platform“.

How to Extract the Last N-Word in Excel

For demonstration, we will use the following string “Hello Geeks, Welcome to the GeeksforGeeks Platform”. As an example, We will extract the last 5 words of the given string. Follow the below steps,

Step 1: Count the number of spaces in the string. The formula for counting spaces,

LEN(cell_name)-LEN(SUBSTITUTE(cell_name,” “,””))

Example:

Output

Step 2: Replace the fifth space from the last with any special character like @. Formula is,

SUBSTITUTE(cell_name,” “,”@”,(LEN(cell_name)-LEN(SUBSTITUTE(cell_name,” “,””))-5+1)).

Example:

Output

Step 3: Get the location of the special character. Formula is,

LEN(cell_name)-FIND(“@”,SUBSTITUTE(cell_name,” “,”@”,(LEN(cell_name)-LEN(SUBSTITUTE(cell_name,” “,””))-5+1)))

Example:

Output

Step 4: Extract all the characters after the special characters. Formula is,

RIGHT(cell_name,LEN(cell_name)-FIND(“@”,SUBSTITUTE(cell_name,” “,”@”,(LEN(cell_name)-LEN(SUBSTITUTE(cell_name,” “,””))-5+1))))

Example:

Output

So, the final formula to get the last 5 words from the string is,

 = RIGHT(cell_name,LEN(cell_name)-FIND(“@”,SUBSTITUTE(cell_name,” “,”@”,(LEN(cell_name)-LEN(SUBSTITUTE(cell_name,” “,””))-5+1))))

Replace 5 with the value of N to get the last N words from the string.

Formula to Extract Last-N words in Excel

 = RIGHT(cell_name,LEN(cell_name)-FIND(“@”,SUBSTITUTE(cell_name,” “,”@”,(LEN(cell_name)-LEN(SUBSTITUTE(cell_name,” “,””))-N+1))))

In this article, I’ve shown you how to get the last n words out of a cell. Hopefully, this helps you out, I advise you to read more articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!

FAQs on How to Extract the Last-N Words in Excel

Q1: What is the need to extract the last N words in Excel?

Answer:

Extracting the last N words in Excel is helpful when you want to focus only on the end part of the text. It’s useful for analyzing recent or relevant information, such as the final words in a sentence or paragraph.

Q2: How to extract the last N words from a range of cells in Excel?

Answer:

Apply the same extraction technique to a range of cells in Excel as you extract the last N words from each cell. Excel will calculate the last N words for each cell, helping you efficiently process multiple pieces of data.

Q3: What are the precautions to take when using this technique?

Answer:

You should always double-check your formulas to avoid errors. Your data should be clean and consistent, as extra spaces or non-standard characters might affect the results.

Q4: What are the other alternative methods to extract the last N words in Excel?

Answer:

Apart from using the RIGHT and the LEN Functions, you can achieve similar results with other text- manipulation functions like MID, FIND, OR SUBSTITUTE. You can choose methods according to your data and requirement.