Rifat Hassan
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 first character in Excel. Here we will discuss 6 of them using this dataset as an example.
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.
- 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.
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.
- Now, drag the Fill Handle to do the same for the rest of the cells as well.
At last, we will get results like the following.
Read More: Excel Remove Characters From Right
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.
- Then select the cell D5 and write the following formula in the cell.
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.
Again, using the Fill handle, we will get result like the following.
Read More: How to Remove Last Character in Excel
Similar Readings
- How to Remove Characters from Left and Right in Excel
- How to Remove Semicolon in Excel
- How to Remove Apostrophe in Excel
3. Using REPLACE Function to Remove First Character in Excel
Using the REPLACE function of Excel 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 separate column for Corrected Student ID‘s.
- Secondly, select the first cell of the column D5 and write the following formula:
=REPLACE(C5,1,1,"")
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.
- Finally, drag the Fill Handle to get the full result.
Read More: How to Remove First 3 Characters 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.
- Then go to the Data tab in the Ribbon and select the Text to Column option under the Data Tools section.
- As a result, we will get a dialogue box called Convert Text to Column Wizard. Click on Next.
- 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.
- At last, in the next step, click on the Do not import column (skip) option and then press Finish.
- Finally, we will get results like the following.
Read More: How to Remove Special Characters in Excel
Similar Readings
- How to Remove Last Digit in Excel
- How to Remove the Last 3 Characters in Excel
- How to Remove Characters After a Specific Character in Excel
- How to Remove Numeric Characters from Cells in Excel
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.
- 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’.
- Now we will drag the Fill Handle for the rest of the cells.
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.
🌠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.
Read More: How to Remove Specific Characters in Excel
6. Using 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.
🔶 Step 2: Inserting VBA code
- First, click on Insert and then Module. A module box will appear.
- 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
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.
- Then go to the Developer tab in the Ribbon and select Macros.
- Next, click on Remove_First_Characters and press on RUN.
- Finally, we will get results like the following.
Read More: How to Remove the First Character from a String in Excel with VBA
Similar Readings
- How to Remove Asterisk in Excel
- How to Remove Blank Characters in Excel
- How to Remove Characters from String Using VBA in Excel
- 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.
Generic 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.
- Next, press Enter to see the result.
- Furthermore, drag the Fill Handle to fill up the rest of the cells.
- 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.
Generic Formula:
Here the difference is the LEFT function starts taking data from the left side.
Read More: How to Remove Characters from Left 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.
Related Articles
- How to Remove Parentheses in Excel
- How to Remove Non-Printable Characters in Excel
- How to Remove Non-numeric Characters from Cells in Excel
- How to Remove Dashes from Phone Number in Excel
- How to Remove Dashes in Excel
- How to Remove Dashes from SSN in Excel
- How to Remove Non-Alphanumeric Characters in Excel
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.
3. Now, write intl.cpl in the Open: text box.
4. Subsequently, click on the OK button.
5. As a result, the Region window will appear.
6. Following, click on the Additional settings… button.
7. Consequently, the Customize Format window will appear. Here, you would see the List separator: option is selected as semicolon (;).
8. Following, change the List separator: option to comma (,) >> click on the Apply button >> click on the OK button.
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