How to extract first or last two or n words from text string?

When working with large sets of text data in Excel, it is often necessary to extract certain words from within each cell, such as the first few words, the last words, or words in a specific position. Common scenarios include extracting names, addresses, keywords, or unique identifiers from cells containing multi-word text entries, especially when data is separated by spaces. For example, you may have a cell containing a sentence or product code, and you want to pull out the first three words, or just the last two words for downstream processing. Excel does not have a direct built-in function for this, but there are multiple approaches to achieve flexible word extraction, each suited to different data sizes and needs. This article will introduce formulas, practical tools, and additional automation techniques to help you efficiently extract the first, last, or nth word(s) from a text string in Excel.

Extract first two or n words from text string with formulas

Extract last two or n words from text string with formula

Extract the first or Nth word from text string with a handy feature

Extract words with VBA macro (first, last, or nth word)

Extract words using Power Query (split and recombine)

Extract first two or n words from text string with formulas

To extract the first several words from a cell value, you can use the following formulas. This is especially helpful if you have a consistent word delimiter (such as a space), and you wish to take, for example, the first three words from a cell. These formulas are easy to apply, work well for moderately-sized datasets, and can be adapted to extract any number of leading words.

Enter either of the formulas below in a blank cell (such as C2), and then drag the fill handle down to fill the formula for all relevant rows. You will see the desired number of leading words extracted from each text string, as illustrated in the screenshot below.

Extract last two or n words from text string with formula

To extract the last words from a text string (such as the last two or last n words), the following formula will help. This method is effective for datasets where you need to consistently extract trailing words, such as last names, file extensions, or identifiers at the end of a text cell.

Please copy and paste the formula below into a blank cell where you want the result. After entering the formula, drag the fill handle down to apply it to other rows.

Extract the first or Nth word from text string with a handy feature

If you need to extract only the first or a specific (nth) word from a text string, and prefer a solution that does not require memorizing or editing complex formulas, you can use Kutools for Excel. The Extract the nth word in cell feature allows you to pick out a specific word position from a cell easily, making it suitable for both occasional use and repeated tasks. This approach is recommended if you want a quick, menu-driven solution with minimal risk of errors and without manual formula entry.

After installing Kutools for Excel, please proceed as follows:

1. Select a cell where you want to display the extracted result. Then go to Kutools > Formula Helper > Formula Helper, as shown in the screenshot:

2. In the Formula Helper dialog, carry out these steps:

• Select Text from the Formula type dropdown list.
• Choose Extract the nth word in cell from the formula list box.
• Specify your source cell in the Cell textbox, and type the desired word position (e.g., 2 for second word) in the The Nth textbox.

3. Click OK to return the result, then drag the fill handle down to apply the same logic to the rest of your list. See the illustration:

This method is suitable when extraction requirements change often, or when you want a guided interface instead of building custom formulas each time.

Extract words with VBA macro (first, last, or nth word)

For more advanced use cases—such as dynamically extracting the first, last, or any specific nth word from text, or when handling large or frequently updating datasets—a VBA macro provides more flexibility and automation than formulas alone. VBA lets you create a reusable tool that can process many cells quickly, letting you customize which word or group of words to extract based on your needs. It can be especially useful if you regularly need to manipulate text in the same way or want to avoid complex nested formulas.

Typical scenarios: Bulk extraction for imported text, batch cleaning, or preparing standardized reports.

1. Open the VBA editor by clicking Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, select Insert > Module and paste the following code into the new module:

Sub ExtractWord()
    Dim Rng As Range
    Dim Cell As Range
    Dim WordPos As Integer
    Dim Words() As String
    Dim ExtractedWord As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set Rng = Application.Selection
    Set Rng = Application.InputBox("Select range to extract word from", xTitleId, Rng.Address, Type:=8)
    
    WordPos = Application.InputBox("Enter which word number to extract (1=first,2=second, etc., or -1 for last)", xTitleId, 1, Type:=1)
    
    Application.ScreenUpdating = False
    
    For Each Cell In Rng
        Words = Split(Application.Trim(Cell.Value), " ")
        
        If WordPos = -1 Then
            If UBound(Words) >= 0 Then
                ExtractedWord = Words(UBound(Words))
            Else
                ExtractedWord = ""
            End If
        ElseIf WordPos >= 1 And WordPos <= UBound(Words) + 1 Then
            ExtractedWord = Words(WordPos - 1)
        Else
            ExtractedWord = ""
        End If
        
        Cell.Offset(0, 1).Value = ExtractedWord
    Next Cell
    
    Application.ScreenUpdating = True
    
    MsgBox "Extraction complete! Results placed in the column to the right.", vbInformation, xTitleId
End Sub

2. Run the macro by clicking the Run button or pressing F5. The macro will prompt you to select your data range, ask which word position to extract (for example, 1 for first word, -1 for last word), and then place the extracted word in the adjacent column to the right. Make sure your delimiter is a space, or adapt the Split function for other delimiters as needed.

This VBA approach makes repeated, bulk extraction much faster and is ideal when formula-based solutions become cumbersome. Note: Always save your workbook before running macros, and ensure that macros are enabled in your security settings.

Extract words using Power Query (split and recombine)

When managing large tables or recurring import/export tasks, Power Query offers a robust alternative for extracting and manipulating words from text strings. Power Query, found under the Data tab as Get & Transform Data, lets you visually split text by delimiters (spaces, commas, etc.), extract the required word(s), and then recombine or load data back to Excel. This process is highly repeatable and suitable for bulk data cleaning, especially when dealing with hundreds or thousands of rows.

Main advantages: Consistency for recurring use, visual interface, easy to adjust if word position requirements change.

Operation steps:

1. Select your data range and choose Data > From Table/Range to load your data into Power Query. If prompted, confirm/create your table.
2. In Power Query Editor, select the column with your text.
3. Click Split Column > By Delimiter

   

4. Select Space or input your custom delimiter, then choose Split at – Each occurrence of the delimiter. Then click OK.

   

5. Each word from the text will now appear in its own column (e.g., Column1, Column2, etc.). If you wish to extract the first two words, recombine the split words. Select the corresponding columns, right click the header, then select Merge Columns from the context menu.

   

6. Choosing your preferred delimiter (e.g., space or comma) and click OK.

   

7. Finally click Close & Load to load your results back into a new worksheet of the current workbook.

Power Query also makes it effortless to re-use the process, as you can refresh the query if your underlying table changes, ensuring consistent results without manual re-application of formulas or macros. Remember to review the number of columns Power Query produces and account for missing words in shorter text strings.

More relative articles: