Excel VBA: How to get first N words from a string

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.

Setup

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?

Does this article help you? If so, please consider supporting me with a coffee ☕️

An Attempt to Use LEFT()

Working with the text above, let’s see if we can use the LEFT() function to help us out:

=LEFT(A1, 100)

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.

=LEFT(A1, 100)&"..."

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 num_of_chars?

=LEFT(A1, 60)&"..."

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 wordCount As Long
    wordCount = UBound(words) + 1

    Dim result As String
    result = words(0)

    Dim i As Long
    i = 1
    Do While (i < num_of_words And i < wordCount)
        result = result & " " & words(i)
        i = i + 1
    Loop

    GetSummary = result & "..."
End Function

The syntax of this function is:

GetSummary(text, num_of_words)

You can use it like so:

=GetSummary(A1, 10)

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:

["This","is","text"]

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 the 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 the user.

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!