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
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:
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.
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.
= 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:
The result is the same as the first case.