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 first n words from a text string.

Generic Formula to trim text to n words

=LEFT(cell_ref,FIND("*",SUBSTITUTE(cell_ref," ","*",n))-1)

Our goal is to split the text into a given number of words. In the first example, we’ll use regular Excel functions and nested formula to trim text to n words.

The key elements of the formula: SUBSTITUTE, FIND, and LEFT.

Let’s check the formula in C2:

=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",B2))-1)

Evaluating the formula

As usual, we’ll process the formula from the inside out.

We have to apply a marker, in this case, “*”. The marker plays an important rule. Actually, it’s a delimiter! With its help, we can parse the given text string easily.

Generic Formula to trim text to n words

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

=SUBSTITUTE(A2," ","*",B2)

The result of the expression:

="Thomas Edison’s inventions shape every day of a modern man or woman’s life*”

Apply the FIND and LEFT function to get rid of the text after the delimiter.

=FIND("*",SUBSTITUTE(A2," ","*",B2))
=76

Because of the “*” character is the 76nth character in the text string, the FIND function gets 75.

To remove the space character, we subtract 1.

=LEFT(A2,76-1)
=LEFT(A2,75)
="Thomas Edison’s inventions shape every day of a modern man or woman’s life."

Get first N words from a string

In this section, we would like to introduce something special! We have sad quite a number of times that you can save your time if you are using user-defined functions.

The GetNWords functions help us to trim text to n words from a string.

The syntax:

= GetNWords(text As String, num_of_words As Long)
Get first N words from a string

Check 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

In the example, apply the function in cell C3 to trim the first 13 words from the text:

=GetNWords(A3,13)

The result is the same as the first case.

Related Formulas and Examples