Learn how to get the first word of a text in Excel – Excel quick tip

By: Ilkka Mäkinen | Published: 10 March, 2016 | In: Excel

How to get the first word of text in Excel

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:

How to use function left in Excel

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.

 

Subscribe to our newsletter

Get the latest expert advice from our blog on Excel, WordPress, search engine optimization, Google analytics and Adwords straight to your email.

 

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:

left-and-search-get-the-first-words-excel

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.

By: Ilkka Mäkinen | Published: 10 March, 2016 | In: Excel

Want to stop messing about and learn to use Excel efficiently?

Save your spot in our Excel Essentials course and learn how to make Excel rock!

Terms & Conditions

Conditions: Minimum number of participants is 3 persons on each course. If the course you are enrolling in does not reach the minimum number of participants it will be cancelled or postponed. We will inform enrolled participants in case there are not enough participants on a course.

Cancellations and substitutes: a) Cancellations confirmed more than 21 days prior to the date of the course are fully refundable and free of charge. Cancellations are to be made in writing to info@talentwire.eu. 

b) Cancellations made within 21 days of the date of the course may not be cancelled without incurring course charges fully.

c) Substitutes may be made at any time free of charge. TalentWire should be informed of any changes.

Pricing and invoicing: Payment for each course is to be received no later than 14 days prior to the course start date. We will send an invoice after your booking has been confirmed. Inform us of any special arrangements you require for your invoices.

All prices are as stated and are exclusive of VAT which will where appropriate be charged at the current rate.

×