Syntax error How to Extract First or Last or Nth Word from Text String in Excel

How to Extract First or Last or Nth Word from Text String in Excel

Excel is a robust data management and analysis tool, and the ability to extract particular words from a text string can be quite helpful in a variety of situations. Knowing how to extract certain words from a text string can be incredibly useful when working with enormous datasets, assessing survey results, or processing text-based data. It will also make your data manipulation activities go much faster.

You will be well-equipped to manage text-based data after completing this course since you will have a thorough understanding of how to extract particular terms from a text string in Excel. So, let's get started and discover the potential of Excel's word extraction from text strings!

Extract First / Last Word From Text String

Here we will first use a formula for any one of the results, then use the autofill handle to complete the task. So let us see a simple process to know how you can extract the first and last word from a text string in Excel.

Step 1

Consider an Excel sheet where you have a list of text strings.

First, to extract the first words, click on an empty cell and enter the formula as =IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1)) and click enter. Then drag down using the autofill handle.

Empty cell > Formula > Enter > Drag.

Step 2

Now to extract the last word, click on an empty cell and enter the formula as =IF(ISERR(FIND("",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))) and click enter. Then use the auto-fill handle to get all the values.

Empty cell > Formula > Enter > Drag.

Extract Nth Word From Text String

Here we will first create a user-defined formula using VBA and then use it to complete the task. So let us see a simple process to learn how you can extract the nth word from a text string in Excel.

Step 1

Consider the same data that we used in the above example.

First, use Alt + F11 to open the VBA application.

Step 2

Then click on Insert, select Module, and copy the below code into the text box.

Example

Function FindWord(Source As String, Position As Integer)
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
   FindWord = ""
Else
   FindWord = arr(Position - 1)
End If
End Function

Step 3

Then click on the empty cell and enter the formula as =FindWord(A2,3) and click enter. Then drag down using the autofill handle to complete the task.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can extract the first, last, or nth word from a text string in Excel to highlight a particular set of data.

Updated on: 2023-07-12T21:34:13+05:30

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements