Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Top 5 Methods to Remove First Character from a Cell in Excel

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.

1. Combine RIGHT and LEN

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:

=RIGHT(A2,LEN(A2)-1)

remove first character in excel cell using text to column with right len

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

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.

=REPLACE(A2,1,1,””)

remove first character in excel cell using text to column with replace

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

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:

=MID(A2,2,LEN(A2)-1)

remove first character in excel cell using mid len

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. With Text to Column

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.
remove first character in excel cell using text to column select data
  • After that, go to ➜ Data ➜ Data Tools ➜ Text To Column. Where you’ll get a dialog box.
remove first character in excel cell using text to column click on
  • From here, select “Fixed Width” and click next.
remove first character in excel cell using text to column select fixed width
  • In the data preview, click between first and second character to insert a vertical line between both of the characters.
  • And then click next.
remove first character in excel cell using text to column click between
  • Now from the data column format, select format type you want to apply and click finish.
remove first character in excel cell using text to column click finish

Finally, here you have two columns, one with the first character and other with the rest of the characters.

remove first character in excel cell using text to column delete first column

You can delete if you don’t want to use the column with first character.

5. UDF

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)

End Function

  1. First of all, open the visual basic editor from your workbook using Alt + F11.
  2. After that, insert a module and paste above code into it.
  3. Now, come back to your worksheet and insert the following formula.
remove first character in excel cell using text to column using VBA

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

Conclusion

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.

  • ratanak

    Nice,I often use the formula as you said in 4 methods,now I got one more UDF,thanks puneet

    • Puneet Gogia

      Yup, UDF is pretty cool.

  • Torstein S Johnsen

    I find the mid-function the best!

    • Puneet Gogia

      That’s great. Torstein.

  • Syed Waqar Hussain Shah

    I think method 1 is very simple and easy for all levels of users

  • Plastic Cup

    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.