Skip to content

Capture the First or Last Word of Text

In this article, I will show you how to put together formulas for capturing the first or last word from a text string in Google Sheets.

I’ll cover two ways you can capture the first word, and then I will go over how to capture the last word.

So if you’re ready to start learning, then let’s get to it!

Capture the First Word – Method 1

The first method you can use to capture the first word in a text string involves this formula.

=LEFT(text, FIND(" ", text) - 1)

The LEFT function returns a specified number of characters from a text string starting from the left. So in this formula, you reference the text string for the first argument within this function.

The second argument of the LEFT function determines the number of characters returned from the referenced text string. This is where the FIND function comes into play.

The FIND function searches for a specific character and returns the position of that character. In this case, the FIND function is set up to look for the position of the first space.

You then subtract one from this number to calculate the position of the last letter of the first word within the text string.

And this is the number used by the LEFT function, which results in the LEFT function returning the first word from the text string.

Method 1 – Example

Here is an example of method 1.

=LEFT(A2, FIND(" ", A2) - 1)

The LEFT function references the text in cell A2, and the FIND function locates the first space within the text.

=LEFT(A2, 6 - 1)

One is subtracted, and the LEFT function returns the first word from the referenced text.

="Learn"

You can now use the fill handle to drag the formula down for the remaining text values.

Capture the First Word – Method 2

The second method to capture the first word in a text string involves the following formula.

=INDEX(SPLIT(text, " "), 1)

The INDEX function returns the value from an array based on a specified position. The SPLIT function divides a text string into an array of text values based on a specified delimiter.

In this formula, the SPLIT function takes the text string and splits it up based on the spaces resulting in an array of words.

The INDEX function then references this array and returns the first value because a one is specified as the second argument. And, of course, the first value in the array is the first word of the text string.

As a result, this formula gives you the first word from any text string.

Method 2 – Example

Here is an example of method 2.

=INDEX(SPLIT(A2, " "), 1)

The SPLIT function references the text in A2 and splits it into an array of words.

=INDEX( {"Learn", "about", "spreadsheets"}, 1)

The INDEX function then returns the first value in the array, which happens to be the first word from the text string.

="Learn"

You can then use the fill handle to drag down the formula for the remaining text values.

Capture the Last Word

The formula used to capture the last word in Google Sheets is similar to the one used for method 2 of capturing the first word.

=INDEX(SPLIT(text, " "), COUNTA(SPLIT(text, " ")))

The big difference is that the second argument of the INDEX function is replaced with the COUNTA function coupled with the SPLIT function.

Once again, the SPLIT function breaks the text string into an array of words, and the COUNTA function returns a count of how many values there are in the array.

The number of values in the array is also the position of the last value in that array.

This value is passed to the INDEX function, which returns that value giving you back the last word from the original text string.

Capture the Last Word – Example

Here is an example of capturing the last word from a text string in Google Sheets.

=INDEX(SPLIT(A2, " "), COUNTA(SPLIT(A2, " ")))

Just like before, the SPLIT function breaks the text into an array of words that is passed into the first argument of the INDEX function.

=INDEX( {"Learn", "about", "spreadsheets"}, COUNTA(SPLIT(A2, " ")))

For the second argument, the COUNTA function counts the number of values in the array of words generated by the SPLIT function.

=INDEX( {"Learn", "about", "spreadsheets"}, COUNTA({"Learn", "about", "spreadsheets"}))
=INDEX( {"Learn", "about", "spreadsheets"}, 3)

That number is passed to the INDEX function, which returns the last value in the array giving you back the last word from the original text string.

="spreadsheets"

You can then use the fill handle to drag down the formula for the remaining text values.

And these are the methods you can use in Google Sheets to get back the first and last words from a singular text string.

Download the Practice File

You can download the practice file by clicking the link below to try these methods yourself!

Capture the First and Last Word Practice File

Watch the Video

This Post Has One Comment

  1. The video was super helpful! I like the blog post for reference though! It really helps to see it all laid out!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top