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:
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:
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.
="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.
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.
= GetNWords(text As String, num_of_words As Long)
The function uses two required arguments: “text“, and “num_of_words“.
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.
If you want to use the GetNWords function, use our add-in, DataFX.