Learn how to get the first word of a text in Excel – Excel quick tip
The end result of this post: we have extracted the first words of the text in column A to column B
If you need to get the first word of any text in Excel, this is a handy quick tip for you. In this post I will show you how to use the functions LEFT and SEARCH together to achieve this. First, I will go through the LEFT function to extract any number of characters from the beginning of any text. Then I will show how to use function SEARCH to get the first word.
Let's begin with the function LEFT.
How to get any number of characters from the left in Excel
You can get any number of characters starting from the left by using the function LEFT:
This is how it would look like if we want to get the first 5 characters of the text in cell A2:
Let's break it down:
LEFT(select the cell containing text;number of characters you want to return)
That's all there is to it!
Of course you can't use this formula to get all the first words from the whole column. We have defined to return the first 5 characters and the first words in the column A are not all 5 characters long.
To achieve this we need to use the function SEARCH.
Why?
We can use SEARCH function to get the position of the first space between the first two words.
So SEARCH will tell us that in the text "Brick quiz whangs jumpy veldt fox!" the first space is the 6th character from the left.
To get the position of the first space we type:
SEARCH(" ";A2) Here's the breakdown:
SEARCH(text you want to find;where you want to find it from)
So I am looking for a " ", which is space, from cell A2.
Oh, and always type the text you are looking for in quotes or Excel will give you an error.
Good, let's continue!
How to get the first word of any text in Excel
So okay, in this case we have a bunch of sentences in the cells of column A and we want to get the first words of all of them without copy pasting for hours. Here's how we will go about this:
- We will use functions LEFT to return a number of characters from the left
- We will get the position of the first space with SEARCH function, so we will get the first words no matter how long the words are
This is how the function looks like:
LEFT(A2;SEARCH(" ";A2)) And this will give you the first words of all of the cells.
Note: This formula also returns the space. In order to get rid of the space in the end of the words you need to do:
LEFT(A2;SEARCH(" ";A2)-1) Like so:
Let me explain.
SEARCH will give us the number of the first space. So the space after the first word "Brick" is the 6th character from the left.
If we just leave the LEFT function with that, we will get the space to the column B as well, and we might not want that. That's why I take one character out in the end, i don't want that space to tag along.
That's it for this quick tip, I do hope you have enjoyed it. Until next time!
You can learn more tips and the efficient use of Excel in our Excel courses. The Excel Essentials training course is perfect for for beginners and intermediate levels, the Excel Advanced course is the best match for those who want to become more advanced Excel users.