Trim text to n words

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.

Generic Formula to trim text to n words

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

=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 in the first example to trim text to n words.

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.

Let’s check the formula in F3:

=LEFT(B3,FIND("*",SUBSTITUTE(B3," ","*",D3))-1)

Evaluate the formula from the inside out:

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)

Result:

="Thomas Edison was already well known by the time he perfected the long-burning incandescent light bulb,*"

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

=FIND("*",SUBSTITUTE(B3," ","*",D3))
 =104

Because the “*” character is the 105th character 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 the GetNWords function

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“.

Code:

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

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

Formula:

=GetNWords(B3,16)

If you want to use the GetNWords function, use our add-in, DataFX.

Related Formulas and Examples

• Split text and numbers
• Get the first word from a text string
• Split dimensions into two parts
• Get the last word from a text string
• Split text with delimiter
• Split text string at specific character