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:
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.
This is a great tip!
Thank you, first solution was perfect, very clever.
Kudos for the author of this article – it helped pretty fast!
Thank you! First solution was perfect and worked like a charm.
Thanks for your help
this is great !!!!! THANK YOU
Came to learn how to chop off the prefix of a cell, and the right/len combo worked beautifully. Thanks!!
You Are the Star! That saves a ton of Work and clicking! All method are Great! Thanks a lot!
we all are stars.
Genius!! Thank you Saved me so much time taking out that first character to be able to run Vlookup on a spreadsheet.
Great article! Helped me a great deal on my project! Thank you so much
I love you
Thank you for all the examples and options. But, what if I wanted to extract the data as you have described and put that data back into the SAME cell. I don’t want to muddy up my spread sheet with pointless redundant data.
Before someone says copy past or any thing like it!! Just don’t, the point is to have a formula that is not seen on the sheet itself and only the data I need to use in the area that I need it to be located at.
Hey Jared!
Step #4 is the one to use here.
Its the only one I didn’t know about before but it works perfectly to keep things in the same cell.
I have a list of scanned eight character serial numbers but I actually do not want the first two characters:
R90TEZP2
R90TEZP3
R90TEZP4
R90TEZP5
R90TEZP6
R90TEZP7
Text to Columns > Fixed Width > Set the break at 2 > Select “Do not import column (skip) for the first column > Select Text for the second column and Destination equal to the first of my selected cells ($C$249 in this case) > Finish
and I get the above cells changed to:
0TEZP2
0TEZP3
0TEZP4
0TEZP5
0TEZP6
0TEZP7
Thanks to Plastic Cup
Dear Puneet,
To remove more than one character (two, three, four etc) from the string/cell then using a UDF is a perfect way
Would be possible if the number of characters to be removed is inputting by using an Input box (cnt in formula below comes from input box)
removeFirstC = Right(rng, Len(rng) – cnt)
Best regards
Arsil Hadjar
In my opinion right with len combination is much better.
Text to column will my second option
I usually use right(..), although sometimes end up coding mid version. I always avoid writing any VBA as much as possible, so UDF is not a way I’d do it.
Hi Puneet,
Question was asked by a panel to count no cells having January date from given list of dates. How can I do this in single cell (by using array/ or any other way
Date =month() =countif()
12-01-2018 1 3
12-12-2018 12
23-01-2018 1
12-03-3018 3
31-03-3019 3
23-01-2017 1
For me the easiest way is to use method number 3, but with some simplifying. If the goal is just to remove first character you don’t have to bother with ‘LEN()’. Third parameter must be ‘long enough’ to handle all strings (let’s say 50 or 100).
Then
MID(A2,2,100)
works perfectly and is easy enough to write even by unexperienced users.
On the other hand you have to remember that Flash Fill can be more difficult to use. Of course with test cases you’ve prepared Flash Fill works brilliantly.
But if the first string is ‘AAA1’ and the second ‘A001’?
When using Flash Fill you must first learn Excel to divide text into smaller pieces. If you define the first case as the division into ‘A’ and ‘AA1’ then Flash Fill in the second case will divide the entry into ‘A’ and 1, because it will automatically convert ‘001’ to a number. Therefore, using Flash Fill you must first either format cells as text or – in the second row – specify how to deal with ‘A001’ (because the rule for ‘AAA1’ does not work).
That’s why I prefer method number 3 🙂
I loves me some string formulas, text-to-columns though causes Excel to keep trying to split text up long after you have deleted the helper columns, etc. The bestest, easiest, quickest method to split up text (or even numbers) is Flash Fill. In your example, insert a new column C, in cell C2 type 001 and press Enter. Now just press CTRL+E and Excel will fill the rest of the column with the same portion from the text string of every adjacent cell in column B. You can delete column B with no ill effects on your new column, if desired.
Yeah, I second you on this.
Thank you for this! <3 Saved my day trying to standardize some numbers by removing extra proceeding zeros from some file numbers. From G00011111 thru G00000001, getting them to align to G11111 thru G00001.
You’re very welcome!
I think method 1 is very simple and easy for all levels of users
I find the mid-function the best!
That’s great. Torstein.
Nice,I often use the formula as you said in 4 methods,now I got one more UDF,thanks puneet
Yup, UDF is pretty cool.