How to remove the first word of a text in Excel

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

How to remove first word in Excel

The end result of this post: the first "word" is removed from cells in column B.

Wondering how to remove the first word in of a text Excel? Or how to remove the first words dynamically in Excel, if the length of the words varies? No problem! You can achieve both pretty easily if you understand the functions RIGHT, LEN and SEARCH.

Sometimes you get a bunch of text with some redundant words or characters you need to remove before you can start doing the actual work you need to get done. Here's an example:

Example text

There are those numbers in brackets on every row I want to get rid of.

But lucky for us, it is very simple to remove words from the beginning of the text in Excel. If the length of the first words are the same, we just need two functions, RIGHT and LEN. And in our case, the first "word" is always just 2 numbers in brackets so this is exactly what we need.

How to use the function RIGHT in Excel

Function RIGHT  returns characters counting from right like this:

RIGHT(select the cell containing the text;how many characters you want to return)

So if I want to get the last word of "(28) Brick quiz whangs jumpy veldt fox!", which is in cell A2, I would go with:

RIGHT(A2;4)

This function just returns 4 characters from the right.

But this is pretty useless by itself in our case.

First, we want to get rid of the first word of long strings of text and I can't be bothered to count all those characters. Second, I want to go through the whole column and sentences are not all equally long.

So we will use function LEN to count how many characters there are in every cell and from the result, we will subtract the number of characters in the first word. This way we will get the exact length of each text without the first word. Sounds complicated?

Not at all. Here's the breakdown:

LEN(select the cell you want to count characters from)-number of characters you want to substract

Here's how to get the length of the text "(28) Brick quiz whangs jumpy veldt fox!" without the "(28)":

LEN(A2)-5

Why do we remove 5 characters? Because there are 4 characters in "(28)", but we want to remove the following space as well.

Are you still with me? Great! Let's combine the functions to remove the first words in our cells.

Here's the formula:

RIGHT(A2;LEN(A2)-5)

Result here:

Remove first words in Excel end result

 

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.

 

But wait! What if the first words vary in lenght?

If the length of the first words vary, we need to count the length of the first word in each cell by using function SEARCH. We have also talked about this in the post covering hot to get the first word of text in Excel.

Here's how you get the length of the first word in the text "(28) Brick quiz whangs jumpy veldt fox!" (or a "word" as it's just "(28)", a number in brackets):

SEARCH(")";A2)

Breakdown: we search for the ")" starting from the left in cell A2 and Excel returns the position of that character counting from the left.

To remove first words with unknown length let's combine the functions:

RIGHT(A2;LEN(A2)-SEARCH(")";A2))

You will notice that this function returns also the space after the first word, but you can get rid of it like this:

RIGHT(A2;LEN(A2)-SEARCH(")";A2)-1)

That's it for this tutorial, 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.

Until next time, happy coding!

By: Ilkka Mäkinen | Published: 11 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.

×