How 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 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:

=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 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.

=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 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:

=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. 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.
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 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)

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.

2018-07-11T10:24:04+00:00
  • FlyKiller says:

    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 🙂

  • Plastic Cup says:

    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.

    • Puneet Gogia says:

      Yeah, I second you on this.

    • Aly June says:

      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.

      • Plastic Cup says:

        You’re very welcome!

  • Syed Waqar Hussain Shah says:

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

  • Torstein S Johnsen says:

    I find the mid-function the best!

    • Puneet Gogia says:

      That’s great. Torstein.

  • ratanak says:

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

    • Puneet Gogia says:

      Yup, UDF is pretty cool.

  • >