Yesterday, one of my colleagues, who is really passionate to learn Excel these days asked me this:
“Do you know how to remove the first character from a cell in Excel?”
I told him a small formula (which was easy) for that, but he was not satisfied and want another method.
And after that, I told him about five different methods for this but then he got confused about which method is easy.
Even I have created a small UDF to make it super easy to for him to remove any number of characters from the start of a string.
Today, in this post, I’d like to share with you all these methods which I’ve found easy and convenient to remove a character from starting off a string.
Here are they.
Using a combination of RIGHT and LEN is the most suitable way to remove the first character from a cell or a string.
In below example, we have a list of roll numbers where we have alphabet “H” as a prefix.
Here we need to remove “H” and want rest of the string and for this, we can combine RIGHT with LEN to remove this first character.
And the formula which we have used for this is:
Here LEN function first counts the total characters from a cell and then RIGHT extracts the characters from right side leaving the first character.
This way you get the string without the first characters.
Note: If you want to remove more one character then simply replace 1 in the right function with 2, 3, 4 for whatever the characters you want to remove.
And, if you want to use a single formula for that you can use REPLACE Function. In below example, we have used REPLACE function to remove the first character from the cell.
We can simply replace the first character with an empty character using the below formula.
Here REPLACE function simple allows you to replace a character from a cell which is in a specific position with a different character.
To remove the character “H” we have replaced it with a blank character. Read more REPLACE from here.
You can also remove the first character from a cell by combining MID and LEN. This combination works like the method-1 which we have used above.
In below example, we have used this combination to get characters from a cell except for the first most character.
And the formula is:
In this formula, LEN returns the total number of characters in the string and then we have deducted 1 from that number. So, if we have 4 characters in a cell it returns 3.
After that, MID extract the characters from the second position and the which we have got from LEN tells MID how many characters need to extract.
Let’s say instead of removing the first character you need to seprate it from the string, then you also can use text to column.
Here are the steps:
Finally, here you have two columns, one with the first character and other with the rest of the characters.
You can delete if you don’t want to use the column with first character.
To give you more ease we have created a simple UDF. It will exactly do the same which we can do with all above-mentioned methods.
Copy the following code and use the steps to insert in your workbook.
Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
Note: If you want to remove more than one character from the starting of the string then you can enter that number in the function.
In conclusion, I’d like to say that all the above methods are quite easy and convenient to use.
First three methods are based on formulas which are dynamic and on the other hand text-to-column is quick but you need to use again and again so sometimes it comes less handy.
But the important thing is if you want to remove more than one character (two, three, four etc) from the string/cell then using a UDF is a perfect way.
If you ask me method 1 and 5 is my favorite. But here you need to tell me one thing.
Which method do you like the most?
Please share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.