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 from a text string.
This formula simply skips the first character from the text provided and returns the rest of the characters.
In the below example, we have a list of roll numbers where we have alphabet “H” as a prefix.
Here we need to remove “H” and return rest of the string and the combination of RIGHT with LEN can help us.
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.
=RIGHT(A2,LEN(A2)-1)
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.
=REPLACE(A2,1,1,””)
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:
=MID(A2,2,LEN(A2)-1)
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. Applying Flash Fill to Remove First Character using a SHORTCUT KEY
On a quick note, you can also use flash fill to remove the first character from a text string. Let me explain to you how to use it and I’m using the same example data here which I have used in other methods.
- First of all, in the cell A1 enter the first text by skipping the first character from the original text.
- After that, hit enter and come down to the second cell in the column.
- Now, press the keyboard shortcut Control + E to apply a flash fill to apply.
how it works
The moment you press the shortcut key flash fill follows the pattern which you have used in the first entry.
In simple words, when you entered the text in the first cell you skipped the first character from the text…
…and flash fill followed the same pattern return text in the rest of the cells without the first character
6. UDF to Remove First (n) Character(s) from a Cell
To give you more ease I have created a User Defined Function.
It will exactly do the same which we can do what other formulas do. Just copy the following code and use the steps to insert in your workbook.
- 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.
Public Function removeFirstC(rng As String, cnt As Long)
removeFirstC = Right(rng, Len(rng) - cnt)
End Function
how this UDF works
In this UDF, I have simply used the formula which we have applied in the first method.
You just need to refer to the cell where you have the text or you can directly enter the text into the function and then you need to enter a number to specify the character you want to remove.
Yes, that’s it.
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.
Sample File
Download this sample file from here to learn more.
Not all the time you’ll have the first character an actual character. Sometimes it can be an unwanted space. In that situation, you can remove it using the TRIM function.
TRIM removes unwanted characters from the starting and from the ending of a text string.
=TRIM(“ Puneet”)
In the end
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.
More Formulas
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.