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 for him to remove n number of characters from the start of a string.
And 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 we go.
Get my exclusive Excel Productivity Bundle Worth $20, Absolutely Free.
1. Combine RIGHT and LEN to Remove the First Character from the Value
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:
How it works
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.
2. Using REPLACE Function to Remove First Character from a Cell
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.
How it works
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.
3. Combine MID and LEN to Get Values After Removing First Character
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:
How it works
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.
4. Remove First Character with Text to Column from a Cell
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:
- First of all, select the column or range of cells.
- After that, go to ➜ Data ➜ Data Tools ➜ Text To Column. Where you’ll get a dialog box.
- From here, select “Fixed Width” and click next.
- In the data preview, click between first and second character to insert a vertical line between both of the characters.
- And then click next.
- Now from the data column format, select format type you want to apply and click finish.
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.
5. UDF to Remove First (n) Character(s) from a Cell
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)
- First of all, open the visual basic editor from your workbook using Alt + F11.
- After that, insert a module and paste above code into it.
- Now, come back to your worksheet and insert the following formula.
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.