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

October 18, 2017

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?

## 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 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:

``GetSummary(text, num_of_words)``
Param Definition
`text` The text you want to get a summary for. Can be a single range like `A1`.
`num_of_words` The amount of words you want to display before adding the ellipses.

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!

Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Written by Joseph who loves teaching about Excel.