Trim text to n words

trim-text-to-n-words-in-Excel

How to trim text to n words? We will show you various methods (regular functions and VBA) to get the first n words from a text string.

How to trim text to n words

Here is the steps to get the first n words from a text string:

  1. Open Excel
  2. Type =LEFT(cell_ref,FIND(“”,SUBSTITUTE(cell_ref,” “,””,n))-1)
  3. Press Enter
  4. The formula will trim the text to n words.

Formula

In the example, we use the formula below:

=LEFT(cell_ref,FIND(“”,SUBSTITUTE(cell_ref,” “,””,n))-1)

Our goal is to split the text into a given number of words. We’ll use regular Excel functions and nested formulas to trim text to n words in the first example. The key elements of the formula are SUBSTITUTE, FIND, and LEFT functions. But first, we want to extract the first 16 words in the example.

trim-text-to-n-words-in-Excel

Let’s check cell F3 and evaluate the formula from the inside out:

=LEFT(B3,FIND(“”,SUBSTITUTE(B3,” “,””,D3))-1)

We have to insert a delimiter, in this case, “*“; it plays an important role. With its help, we can parse the given text string quickly. For example, in cell D3, set the number of words you want to trim from left; it is 16.

The SUBSTITUTE function searches the “*” character and replaces the nth space character.

=SUBSTITUTE(B3,” “,”*”,D3)

Apply the FIND and LEFT functions to remove the text after the delimiter.

=FIND(“”,SUBSTITUTE(B3,” “,””,D3)) = 104

Because the “*” character is 105th in the text string, the FIND function gets 104. To remove the space character, we subtract 1.

=LEFT(B3,105-1) = LEFT(B3,104)

Get the first N words from a string using GETNWORDS

In this section, we would like to introduce something special: you can save time using user-defined functions. The GetNWords function helps us to trim text to n words from a string.

Syntax:

= GETNWORDS(text As String, num_of_words As Long)

Arguments:

The function uses two required arguments: “text“, and “num_of_words“.

In the example, apply the function in cell F3 to trim the first 16 words from the text.

how-to-use-the-getnthwords-function

You can reach hundreds of user-defined functions using the DataFX free add-in. If you want to implement the function manually, you can use the source code below:

Public Function GetNWords(text As String, num_of_words As Long) As String
    If (num_of_words <= 0) Then
        GetNWords = ""
        Exit Function
    End If

    Dim words() As String
    words = Split(text, " ")
    
    Dim result As String
    result = words(0)

    Dim i As Long
    i = 1
    Do While (i < num_of_words)
        result = result & " " & words(i)
        i = i + 1
    Loop

    GetNWords = result & "..."
End Function