Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Extract First or Last Two or N Words from Text String in Excel
When you need to examine or organise data, Excel's text manipulation capabilities can be immensely helpful. Using a variety of methods, we will walk you through the process of removing the desired words from a text string in this article. Excel's robust functions and formulas let you easily carry out intricate operations on your data. Word extraction from a text string can first seem difficult, but with the appropriate strategy, it becomes a simple operation.
This tutorial will cover a variety of situations. You will discover how to extract a text string's first, last, or a certain number of words. To assure your knowledge, we will give you detailed instructions and examples. Regardless of your level of Excel proficiency, this lesson will give you the knowledge and abilities you need to manage text extraction successfully. Let's begin by utilising Excel's text manipulation features to their full extent.
Extract First or Last Two or N Words from Text String
Here we will first use a formula to get the values, then use the auto-fill handle to complete the task. So let us see a simple process to know how you can extract the first, last, or n words from a text string in Excel.
Step 1
Consider an Excel sheet where you have a list of text strings similar to the below image.
First, to extract the last two or n words from the text string, click on an empty cell and enter the formula as =MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1)))+1,LEN(A2)) and click enter to get the first value.
Then drag down using the autofill handle to complete the task. In the formula, A2 is the address of the cell, and 3 represents the number of words to extract.
Empty cell > Formula > Enter > Drag.
Step 2
Now to extract the first two or n words, click on an empty cell and enter the formula as =TRIM(LEFT(A2, FIND("^",SUBSTITUTE(A2, " ", "^",3)&"^"))) and click enter. Then drag down using the autofill handle to complete the task.
Empty cell > Formula > Enter > Drag.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can extract the first, last, or n words from a text string in Excel to highlight a particular set of data.
3K+ Views
