How to Remove First Character in Excel (6 Quick Ways)

We often need to remove the first character from a cell or a range of cells while working in Excel. In this article, we’ll show you how you can remove the first character from data in Excel.


Download Practice Workbook


6 Quick Ways to Remove First Character in Excel

There are various methods through which we can remove first character in Excel. Here we will discuss 6 of them using this dataset as an example.

A dataset containing Student's Name and ID's

Here we’ve got a data set with the Names of some students and their Student IDs of any class. Our objective is to remove the first characters or the ‘S’ from each 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. The steps are below.

🔶 Steps:

  • First, we will insert a new column beside the Student ID column like the below image.

Creating a separate column for desired output

  • Second, we will select the first cell in the new column or in our case, it’s D5 In that cell, write the following formula.
=RIGHT(C5,LEN(C5)-1)
[Here C5 is the cell reference of the first cell of the column Student ID. You can use your reference cell.]

Using the RIGHT and LEN functions to remove first character in Excel

Formula Breakdown

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

  • Third, press Enter to get the following result where the ‘S’ is removed from the data of cell C5.

Single output using the RIGHT and the LEN functions to remove first character in Excel

  • Now, drag the Fill Handle to do the same for the rest of the cells as well.

Using the Fill Handle feature to get result for the rest of the rows

At last, we will get result like the following.

successfully removed the first character in excel

Read More: How to Remove Characters in Excel (6 Methods)


2. Combining MID and LEN Functions to Delete the First Character in Excel

We can also use the MID function and the LEN function of Excel to remove the first character from the Student IDs. The steps are below.

🔶 Steps:

  • At first create a new column like the below image to get expected results.

Creating a separate column for desired output

  • Then select the cell D5 and write the following formula in the cell.
=MID(C5,2,LEN(C5)-1)

Combining the MID and LEN functions to remove first character in Excel

Formula Breakdown

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

  • Further pressing Enter will give us result like the following.

Single output using the MID and the LEN functions to remove first character in Excel

Again, using the Fill handle, we will get result like the following.

Using the Fill Handle feature to get result for the rest of the rows

Read More: How to Remove Last Character in Excel (Easiest 6 Ways)


3. Using REPLACE Function

Using the REPLACE function of Excel to is also a good approach to remove the first character from the Student IDs. All we need to do is to replace the ‘S’ with a blank space. The steps are described below.

🔶 Steps:

  • Firstly, create a separated column for Corrected Student ID‘s.

Creating a separate column for desired output

  • Secondly, select the first cell of the column D5 and write the following formula:

=REPLACE(C5,1,1,"")

Using the REPLACE function to remove first character in Excel

Formula Breakdown

  • REPLACE(C4,1,1,””) replaces the first character of the string C5 with an empty character (“”).
  • Thus, it returns the string removing the first character.
  • Thirdly, press Enter to get result like the following.

Single output using the REPLACE function to remove first character in Excel

  • Finally drag the Fill Handle to get the full result.

Using the Fill Handle feature to get result for the rest of the rows

Read More: How to Remove First 3 Characters in Excel (4 Suitable Methods)


Similar Readings


4. Use of TEXT TO COLUMNS Feature

We can use the TEXT TO COLUMNS feature to remove the first character in excel. Follow the steps below for this method.

🔶 Steps:

  • First, select all the data from where you want to remove the first characters.

Selecting Data Range where we will apply this method

  • Then go to the Data tab in the Ribbon and select the Text to Column option under the Data Tools section.

Using Excel Ribbon to apply the Text to Columns feature

  • As a result, we will get a dialogue box called Convert Text to Column Wizard. Click on Next.

Using the Convert Text to Columns Wizard window to remove first character in excel

  • Again, in this dialog box, select Other and write S in the box beside the Other We will see the S is now excluded from the data in the Data Preview section. Click on Next.

Using the Convert Text to Columns Wizard window to remove first character in excel

  • At last, in the next step, click on the Do not import column (skip) option and then press Finish.

Using the Convert Text to Columns Wizard window to remove first character in excel

  • Finally, we will get results like the following.

successfully removed the first character in excel using the Text to Columns feature

Read More: How to Remove Special Characters in Excel


5. Utilizing Auto Fill Feature to Remove First Character in Excel

It is a very simple approach applicable for a lower amount of data. We will use the Fill Handle in this method.

🔶 Steps:

  • In the beginning, we will create a separated column like the image below. Creating a separate column for desired output
  • Consequently, in the adjacent cell of first data, write the data without the unexpected letter or sign. Here we will write Student Id of cell C5 in the cell D5 without ‘S’.

Writing the first data removing the first character in excel

  • Now we will drag the Fill Handle for the rest of the cells.

utilizing the Auto Fill feature to remove first character in Excel

Here we can see all the cells are containing the same data.

  • Finally, to get the actual result, click on the small box at the bottom and select Fill Series.

Selecting Fill Series option to get expected results for rest of the cells

🌠Shortcut

A smarter version of Auto Fill is the Flash Fill feature. After inserting the first data, we can also go for this step to get the result. All we need to do is to go to the Data tab in the Ribbon and select Flash Fill under the Data Tools section.

Using Flash Fill from the Data tab in the Ribbon to remove first character in Excel

Read More: How to Remove Specific Characters in Excel


6. Applying VBA code to Remove First Character in Excel

If removing the first character in excel is just a part of a bigger process, then we can go for this VBA method. There are several segments of this process. All of them are below.

🔶 Step 1: Triggering VBA Window

  • Go to the Developer tab in the Ribbon and click on Visual Basic. It will trigger the VBA window called Microsoft Visual Basic for Application.

Get Visual Basic from the Developer tab in the Ribbon

🔶 Step 2: Inserting VBA code

  • First click on Insert and then Module. A module box will appear.

Inserting a VBA Module

  • Second, in the box, write the following 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

Inserting code in the Module

Code Breakdown

  • Here we have called a Subroutine named Remove_First_Characters.
  • After that we called a loop for changing each of the data.
  • Then for a specific selection, we have replaced the first character with a blank space or nothing using the REPLACE and LEFT function like we used in the earlier methods.

  • After writing the code, exit the window and go to the excel sheet where we want to apply this method.

🔶 Step 3: Applying the VBA code

  • Select the cell range where we want to apply the method like the image below.

Selecting Data range to remove first character in excel

  • Then go to the Developer tab in the Ribbon and select Macros.

Selecting macros from the Developer tab

  • Next click on Remove_First_Characters and press on RUN.

Applying the Subroutine to remove first character in Excel

  • Finally, we will get results like the following.

successfully removed the first character in excel using VBA code

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


How to Remove First (or Last) n Characters in Excel

We can also remove the first or last n number of characters from a data in Excel. To do so, follow these steps.

🔶 Steps:

  • Let’s say we want to remove the first 4 (n=4) characters in Excel. So first, we will select the destination cell and write the following formula.
=RIGHT(C5,LEN(C5)-4)

Generic Formula:

=RIGHT(cell,LEN(cell)-n)

Using the RIGHT and LEN functions to remove first 4 characters in Excel

Here, C5 is the reference cell that contains the data we want to change. The RIGHT function takes the data from the right side while the length of the data is determined by LEN function. So, we get the data without the first 4 characters.

  • Next, press Enter to see the result.

successfully removed the first 4 character in excel

  • Furthermore, drag the Fill Handle to fill up the rest of the cells.

Using the Fill Handle feature to get result for the rest of the rows

  • Furthermore, drag the Fill Handle to fill up the rest of the cells.
  • Similarly, for the last 4 characters, we will write the following formula and repeat previous steps.
=LEFT(C5,LEN(C5)-4)

Generic Formula:

=LEFT(cell,LEN(cell)-n)

Here the difference is the LEFT function starts taking data from the left side.

Using the LEFT and LEN functions to remove first 4 characters in Excel

Read More: How to Remove Last Character from String in Excel (5 Easy Ways)


Things to Remember

  • The REPLACE function replaces the ‘S’ with nothing or blank. You can use any other sign or word according to your need.
  • Text to Columns feature just deletes the ‘S’ from the data.
  • Flash Fill feature is best for lower numbers of data or rows.
  • The VBA method is irreversible. So keeping a back-up is recommended.

Conclusion

That was all the possible methods to remove the first character in excel. Hope this article will help you. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any excel-related problems, you can visit our website Exceldemy for solutions.


Related Articles

Rifat Hassan

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

2 Comments
  1. None of these worked. Using Office 365, whatever the latest version is, semicolons are used rather than commas, and even so the MID Function does not work properly. Maybe it’s because it’s an international numbering system (this should be mentioned more in help forums, the use of commas, decimals, and semicologs across national border)

    Maybe I’ll figure it out.

    • Hello, John!
      I hope you are doing well.
      About your problem, I think your windows list separator is set to semicolon instead of comma. In this regard, follow the steps below to solve your problem.
      1. Go to your Windows menu and search for the Run window.
      2. Afterward, open the Run window.
      Open Run Window
      3. Now, write intl.cpl in the Open: text box.
      4. Subsequently, click on the OK button.
      Run Window
      5. As a result, the Region window will appear.
      6. Following, click on the Additional settings… button.
      Region Window
      7. Consequently, the Customize Format window will appear. Here, you would see the List separator: option is selected as semicolon (;).
      Customize Format Window
      8. Following, change the List separator: option to comma (,) >> click on the Apply button >> click on the OK button.
      Customize Format Window
      9. Now close your Excel app and reopen the file. The formula will work fine.
      Thus, I hope your problem would be solved now. If it doesn’t, please let us know in the comment section again.

      Thanks,
      Tanjim Reza

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo