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.
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.
It’s a simple formula which simply skips the first character from the value and returns rest of all. In the below example, you have a list of roll numbers where we have alphabet “H” as a prefix.
Here you need to remove “H” and return rest of the string. And for this, we can combine RIGHT with LEN to remove the first character “H”.
Here’s what you need to do, select the cell B2 and enter the below formula into it and after that drag that formula to the end.
In the result, it has returned the last three characters by removing the first character from the actual text string.
but how this formula works
First of all, we have used LEN function which counts the total number of character from the cell and returns that number.
After that, we have used the RIGHT function to get the characters from the same cell starting from the right.
As you know, you need to specify the number of characters you want to extract using the RIGHT function, and for this, we have used the number returned by the LEN…
…but, instead of returning all the character we have subtracted one from it so that the formula skips the last character from the right which is the first character from the left.
If you want to remove more that 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, you can use REPLACE function.
In below example with the same list of roll numbers, we have used REPLACE to remove the first character from the cell.
It simply replaces the first character with an empty character using the below formula.
how it works
With REPLACE function, you can replace a character or more than one character using the starting position of the character(s).
Here in this formula, we have referred to the main text string and specify the starting position 1 (from the characters should be replaced) and then mentioned the number of characters to replace.
As we have specified the starting position 1 and only 1 character to replace and skip specifying the character to replace with.
It has removed the first character and only the first one without replacing it with any other character. That’s why what we got in return is the text string without the first character.
There is no such difference between this method and the method which we have used above.
If you want to remove more that one character then simply replace 1 (characters to replace) with 2, 3, 4 for whatever the characters you want to remove.
3. Combine MID and LEN to Get Values after Removing First Character
The third formula which we can use is a combination is of MID and LEN. sThis combination works just like the two methods 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 this formula calculates
In this formula, LEN returns the total number of characters in the string.
As we want to remove the first character from the original text string we need to deduct 1 from the number returned by the LEN.
So when LEN returns 4 as the total number of characters we deduct 1 from it.
After that in the MID, we have referred to the original text string and mentioned the starting position as two so that it skips the first character.
In the result, the formula has returned the rest of the characters except the first.
4. Remove First Character with Text to Column from a Cell
If instead of removing the first character if you want to separate it from the original text string then you can use TEXT to COLUMN method.
By using TEXT to COLUMN you can split the first character and rest of the characters into two different columns instead of removing it.
Below are the simple steps you need to follow here:
- First of all, select the column or range of cells where you have original text string.
- After that, go to ➜ Data ➜ Data Tools ➜ Text To Column, that's where you’ll get a dialog box.
- From here, select the “Fixed Width” option and click next.
- Now in the data preview, click between the first and the second character to insert a vertical line between both of the characters.
- Once you do this 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.
There's one thing you need to understand that this is not a dynamic method to remove a character and if you want to separate more than one character then you just need to adjust the vertical line up to that character.
5. UDF to Remove First (n) Character(s) from a Cell
Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
how this UDF works
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.