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.

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)

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.