How to Remove First Character in Excel (6 Methods)

We often need to remove the first character from a cell or a range of cells while working in Excel. Today I’ll show you how you can remove the first character from a data set in Excel.


Download Practice Workbook


6 Quick Approaches to Remove First Character in Excel

Here we’ve got a data set with the Names of some students and their Student IDs of a school called Sunflower Kindergarten.

Data Set to Remove First Character in Excel

Today our objective is to remove the first characters from the Student IDs.


1. Use RIGHT and LEN Functions of Excel to Remove the First Character

You can use the RIGHT function and the LEN function of Excel to remove the first character from the Student IDs.

Select a new column and insert this formula in the first column:

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

[Here C4 is the cell reference of the first cell of the column Student ID. You use your one.]

RIGHT and LEN Functions to Remove First Character In Excel

Then drag the Fill Handle to fill this formula to the rest of the cells.

You will find the first character removed from all the IDs.

Explanation of the Formula

  • LEN(C4)-1 returns a number one less than the length of the string C4.
  • Here the length of the string S201678 is 7. So LEN(C4)-1 returns 6.
  • RIGHT(C4,LEN(C4)-1) now becomes RIGHT(C4,6) and returns 6 characters from the right of the string C4.
  • Thus it returns the string removing the first character.

Read more: Remove First Character from String in Excel


2. Combine MID and LEN Functions of Excel to Delete the First Character

You can also use the MID function and the LEN function of Excel to remove the first character from the Student IDs.

Select a new column and insert this formula in the first column:

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

[Here C4 is the cell reference of the first cell of the column Student ID. You use your one.]

MID and LEN Functions to Remove First Character in Excel

Then drag the Fill Handle to fill this formula to the rest of the cells.

Dragging the Fill Handle to Remove First Character in Excel

You will find the first character removed from all the IDs.

Explanation of the Formula

  • LEN(C4)-1 returns a number one less than the length of the string C4.
  • Here the length of the string S201678 is 7. So LEN(C4)-1 returns 6.
  • MID(C4,2,LEN(C4)-1) now becomes MID(C4,2,6)and returns 6 characters starting from the 2nd character of the string C4.
  • Thus it returns the string removing the first character. 

Read more: How to Remove Last Character in Excel


3. Use REPLACE Function of Excel to Remove the First Character

You can also use the REPLACE function of Excel to remove the first character from the Student IDs.

Select a new column and insert this formula in the first column:

=REPLACE(C4,1,1,"")

[Here C4 is the cell reference of the first cell of the column Student ID. You use your one.]

Then drag the Fill Handle to fill this formula to the rest of the cells.

Dragging the Fill Handle to Remove First Character in Excel

You will find the first character removed from all the IDs.

Explanation of the Formula

  • REPLACE(C4,1,1,"") replaces the first character of the string C4 with an empty character (“”).
  • Thus it returns the string removing the first character. 

Read more: How to Remove Characters in Excel


4. Run Text to Column Tool from Excel Toolbar to Erase the First Character

You can run the Text to Column tool from the Excel toolbar to remove the first character from a string.

Step 1:

First, select the column from where you want to remove the first characters (Column C in this example).

Then go to the Data > Text to Column tool in the Excel toolbar under the section called Data Tools.

Text to Column Tool to Remove First Character in Excel

Step 2:

Click on Text to Columns. You will get a dialogue box called Convert Text to Column Wizard.

Next, put a check on Fixed Width. Then click Next.

Step 3:

Then in the Data preview section, insert a horizontal line between the first character and the rest of the characters.

Again click Next.

Text to Column Wizard Box to Remove First Character in Excel

Step 4:

Finally, click on Finish.

Text to Column Wizard Box to Remove First Character in Excel

Step 5:

The selected column will be split into two columns. The first characters are in one column, and the rest characters are in another column.

Copy the second column.

Step 6:

Then paste it over the first column.

Thus you will get the first characters removed from the column.

Output to Remove First Character in Excel

Read more: How to Remove Special Characters in Excel


5. Apply Flash Fill to Remove the First Character in Excel

Step 1:

First, go to the first cell of a new column and enter the first string without the first character manually.

Here I am going to cell D3 and entering 201678.

Filling First Cell to Remove First Character in Excel

Step 2:

Next press Enter. You will be directed to the next cell.

Then press CTRL+E. You will find all the cells will be filled with text values without the first character.

Read more: How to Remove Specific Characters in Excel


6. Use a Macro to Delete the First Character in Excel

This is the last method.

If all the above-described methods can’t satisfy you, you can utilize this method to remove the first characters from a bunch of cells in Excel.

Visit this post to see how to save and run a Macro in Excel.

First, insert this VBA code in a new module:

Code:

Sub Remove_First_Characters()
For i = 1 To Selection.Rows.Count
    Selection.Cells(i, 1) = Replace(Selection.Cells(i, 1), Left(Selection.Cells(i, 1), 1), "")
Next i
End Sub

VBA Code to Remove First Character in Excel

Then select the column and run this Macro called Remove_First_Characters.

And you will find the first characters removed automatically from the selected column.

Output to Remove First Character in Excel

Read more: How to Remove the First Character from a String in Excel with VBA


Conclusion

Using these methods, you can remove the first character from a cell or a range of cells in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo