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. There are various methods through which we can remove the 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. Using RIGHT and LEN Functions to Remove First Character in Excel

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 results like the following.

successfully removed the first character in excel

Read More: How to Remove First 3 Characters in Excel


2. Delete the First Character Combining MID and LEN Functions 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 the 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 results 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 results 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


3. Using REPLACE Function to Remove First Character in Excel

Using the REPLACE function of Excel is also a good approach to removing 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 separate column for Corrected Student ID‘s.

Creating a separate column for desired output

  • Next, 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.
  • Then, 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 Last Digit in Excel


4. Using Excel Text to Columns Feature to Remove First Character

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


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

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

🔶 Steps:

  • In the beginning, we will create a separate column like the image below. Creating a separate column for desired output
  • Consequently, in the adjacent cell of the first data, write the data without the unexpected letter or sign. Here we will write the Student ID of cell C5 in 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 contain 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 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


6. Using VBA Code to Remove First Character in Excel

We can use  Excel VBA to remove the first character from the string if it is just a part of a bigger process. 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

  • Next, 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 Last Character from String Using VBA in Excel


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

We can also remove the first or last n number of characters from 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 the 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 the 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 the Last 3 Characters in Excel


Things to Remember

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

Download Practice Workbook


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.

<< Go Back To Excel Remove Characters From Left | Excel Remove Characters | Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo