How to remove the first word of a text 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:
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:
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!