Last week we talked about Excel’s
LEFT(), RIGHT(), and MID() functions.
This week I’d like to show an example of how you can get the first N words from
a string. We’ll look at using the
LEFT() function along with using a simple
VBA function to accomplish a more sophisticated version.
Let’s say you had some long text and you wanted to provide a “teaser” or a blurb of that text. Here’s the first paragraph of a summary of the book Edison by Alexander Kennedy:
Thomas Edison’s inventions shape every day of a modern man or woman’s life. If today you’ve listened to recorded music, watched a television show or movie, plugged something into an electric socket, had an X-ray, turned on your car, spoke on the telephone, or flicked on an electric light, you have Edison to thank for his pioneering work in these fields, and many more.
- Alexander Kennedy
How can we get the first N words from this paragraph?
An Attempt to Use LEFT()
Working with the text above, let’s see if we can use the
LEFT() function to
help us out:
This asks Excel to get the first
100 characters of the long text:
Thomas Edison’s inventions shape every day of a modern man or woman’s life. If today you’ve listened
But, let’s add ellipses on there to make it more like a teaser.
Thomas Edison’s inventions shape every day of a modern man or woman’s life. If today you’ve listened...
This doesn’t ALWAYS work, though. What if you chose
60 as the
Thomas Edison’s inventions shape every day of a modern man o…
You probably wouldn’t want to cut off any text like this. Plus, it would be more intuitive if you could ask Excel to hand back a certain amount of words than a certain amount of characters. So how can we do that?
We can go about that a couple of ways. One way is with a very complicated text formula. Another is to use VBA to make a UDF (User-Defined Function). I like simplicity over complexity, and I’m sure you do, too, so we’ll choose the VBA option.
VBA User-Defined Function
Going for the VBA solution, we can use the following UDF:
Option Explicit Public Function GetSummary(text As String, num_of_words As Long) As String If (num_of_words <= 0) Then GetSummary = "" 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 GetSummary = result & "..." End Function
The syntax of this function is:
||The text you want to get a summary for. Can be a single range like
||The amount of words you want to display before adding the ellipses.|
You can use it like so:
This asks Excel to return the first 10 words of the string:
Thomas Edison’s inventions shape every day of a modern man...
How this works
The way this macro works is by taking the text handed in, then splitting that
text by its spaces. This is where the
Split() VBA function comes in. After
that, we rebuild the text up to the Nth word that the user asked for.
Split() will take some text, and you pass in a delimiter to split the text up
by. For example:
Split("This is text", " ")
This would return an array of the following:
Notice how the spaces are completely removed? When you
Split on a text, the
delimiter you choose will act as guidelines from where to cut the text up by and
Split function will discard the delimiter from the final result.
Then we build up the resulting string by taking the first word, then appending a
space and the next word, until we have reached the
num_of_words passed in by
Thomas Edison’s inventions shape every day of a modern man... 1 2 3 4 5 6 7 8 9 10
I hope that this has been helpful for you. If you have other ways you would approach this or would like help with another way to manipulate text, please let me know if the comments below!