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

Here we’ve got a dataset with the Names of some students and their Student IDs of any class. We need to remove the first character (the ‘S’) from each Student ID.

Method 1 – Using RIGHT and LEN Functions to Remove the First Character in Excel

Steps:

• Insert a new column next to the Student ID column.

• Select the first cell in the new column, D5.
• In that cell, copy the following formula:

• Press Enter to get the following result where the ‘S’ is removed from the data in cell C5.

• Drag the Fill Handle to do the same for the rest of the cells as well.

• We will get results like the following.

Read More: How to Remove First 3 Characters in Excel

Method 2 – Delete the First Character by Combining MID and LEN Functions in Excel

Steps:

• Create a new column for the results.

• Select the cell D5 and input the following formula:

• Press Enter.

• Double-click the Fill handle.

Read More: How to Remove Last Character in Excel

Method 3 – Using the REPLACE Function to Remove the First Character in Excel

Steps:

• Create a separate column for Corrected Student IDs.

• Select the first cell of the column, D5, and input the following formula:

• Press Enter to get the result.

• Drag the Fill Handle to get the full results for the column.

Read More: How to Remove Last Digit in Excel

Method 4 – Using Excel Text to Columns Feature to Remove the First Character

Steps:

• Select all the data from where you want to remove the first characters.

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

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

• In the second dialogue, select Other and write S in the box next to it. The S will be excluded from the data in the Data Preview section.
• Click on Next.

• In the third dialogue, click on the Do not import column (skip) option and press Finish.

• We will get results like the following.

Method 5 – Applying Auto Fill Feature to Remove the First Character in Excel

Steps:

• Create a separate column for the results.
• Manually write the data without the first character. We will write the Student ID of cell C5 in cell D5 without the ‘S’.

• Drag the Fill Handle for the rest of the cells.

• All the cells will initially contain the same data.
• To get the actual result, click on the small box at the bottom and select Fill Series.

Method 6 – Using VBA Code to Remove First Character in Excel

Steps:

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

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

• In the box, copy 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

• Exit the window and go to the Excel sheet.
• Select the cell range.

• Go to the Developer tab in the Ribbon and select Macros.

• Click on Remove_First_Characters and press on Run.

• We will get results like the following.

Read More: How to Remove Last Character from String Using VBA in Excel

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

Let’s say we want to remove the first 4 (n=4) characters from the cells in our dataset.

Steps:

• Select the destination cell and copy the following formula.

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.

• Press Enter to see the result.

• Drag the Fill Handle to fill up the rest of the cells.

• Similarly, for the last 4 characters, we can use the following formula and repeat the previous steps:

The LEFT function starts taking data from the left side.

Read More: How to Remove the Last 3 Characters in Excel

Download Practice Workbook

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