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.

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.

remove-the-first-character-from-excel-from-a-roll-number-list

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)
remove first character in excel cell using text to column with right len

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.

excel-remove-first-character-len-function-returns-the-number-of-characters

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.

tip

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,””)

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

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)
remove first character in excel cell using mid len

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.
    remove-first-character-in-excel-cell-using-text-to-column-select-data
  • After that, go to ➜ Data ➜ Data Tools ➜ Text To Column, that's where you’ll get a dialog box.
    remove-first-character-in-excel-cell-using-text-to-column-click-on
  • From here, select the “Fixed Width” option and click next.
    remove-first-character-in-excel-cell-using-text-to-column-selct-fixed-width
  • 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.
    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.

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.

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.
remove first character in excel using flash fill

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

Public Function removeFirstC(rng As String, cnt As Long)

removeFirstC = Right(rng, Len(rng) - cnt)

End Function

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

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

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.

About the Author

Puneet Gogia

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.

16 thoughts

Leave a Comment

Your email address will not be published.

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

    Reply
    • 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

      Reply
  2. 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

    Reply
  3. 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.

    Reply
  4. 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

    Reply
  5. 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 🙂

    Reply
  6. 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.

    Reply