6 Ways to Remove the First 2 Characters in Excel
The removal of specific characters from a cell or a group of cells is a common task in Excel. When working with data that contains unnecessary prefixes or codes, it might be very helpful to remove the first two characters. Fortunately, Microsoft Excel offers a variety of ways to get rid of the first two characters. So in this article, I will explore six different ways that will help you to remove the first 2 characters in Excel.
Introduction to the Dataset
In the following dataset, there are two columns, Sample Dataset, and Result. Notice that in the column Sample Dataset, I have a list of full names. Each name is preceded by two characters. The first two characters are sometimes numerical, alphabetical, or symbolic, and some even have combinations of these.
Now I will show you a few methods that will help you remove the first 2 characters in Excel.
Easiest Way to Remove the First 2 Characters in Excel
Using the VBA code can be helpful in removing the first 2 characters in Excel, especially if you need to perform this task repeatedly or on a large dataset. The VBA code will help you receive the results within a few seconds.
This piece of VBA code can smoothly remove the numeric characters from the cells in Excel. Just insert the code inside a new Module in your Visual Basic Editor and then Run it.
Sub RemoveFirstTwoChars()
Dim inputRange As Range
Dim outputRange As Range
Dim inputCell As Range
Dim outputCell As Range
'Specify input range
Set inputRange = Range("A2:A10") 'Replace with your input range
'Specify output range
Set outputRange = Range("B2:B10") 'Replace with your output range
'Loop through input range and remove first two characters of each cell
For Each inputCell In inputRange
Set outputCell = outputRange.Cells(inputCell.Row - inputRange.Row + 1, 1)
If Len(inputCell.Value) >= 2 Then
outputCell.Value = Right(inputCell.Value, Len(inputCell.Value) - 2)
Else
outputCell.Value = ""
End If
Next inputCell
End SubEditable line 1
Set inputRange = Range("A2:A10")Here, the data from where I want to remove the first 2 characters are in the range A2:A10. So, you may need to modify the range according to your dataset.
Editable line 2
Set outputRange = Range("B2:B10")Specify the range where you want to place the output.
Editable line 3 (Optional)
outputCell.Value = Right(inputCell.Value, Len(inputCell.Value) - 2)Here, the RIGHT function eliminates the first 2 characters from the left side. To remove more characters replace 2 with your desired value.
Usage Guide
Step_1: Press ALT + F11 to open the Visual Basic Editor.
Step_2: Then select Insert > Module.
Step_3: Then paste the code into the new module.
Step_4: Click on the Run button or press the F5 key to run the code.
Final Result >
As I ran the VBA code, It instantly filled up the cell range B2:B10 with the output.
Alternative Way #1: Use REPLACE Function to Remove the First 2 Characters in Excel
The REPLACE function in Excel is dedicated to working in a way that replaces an old text with a new text. So, in this method, I am going to use the REPLACE function to replace the first 2 characters with black space.
Syntax
=REPLACE(old_text, start_num, num_char, new_text)Formula
=REPLACE(A2,1,2,””)Usage Guide
Step_1: At first, copy the formula from above.
Step_2: Secondly, paste it into cell B2.
Formula Explanation
- A2 is the old_text here. It indicates the cell address where you want to perform the formula.
- 1 indicates the start_num of the formula. From the left side, the position of the first character is 1.
- 2 is the char_num. The number of characters that I want to remove is 2, that’s why the char_num is 2.
- As I want to replace the 2 characters with empty spaces, for this reason, the new_text in this formula is “ “.
Step_3: Press the ENTER key.
Step_4: Now copy down the formula by dragging the Fill Handle icon from cell B2 to cell B10.
Final Result >
The REPLACE function has removed the first 2 characters successfully.
- 4 Ways to Remove First Word in Excel
- 6 Ways to Remove the First 4 Characters in Excel
- Remove Non-Printable Characters in Excel [5+ Methods]
Alternative Way #2: Use the RIGHT & LEN Function to Remove the First 2 Characters in Excel
The RIGHT function helps to extract a specified number of characters from the right side of a text string in Excel. And, to figure out the number of characters in a given text string in Excel, I will use the LEN function. As an alternative technique to remove the first 2 characters from a cell, I am going to use a combination formula of the RIGHT function and LEN function.
Syntax
=RIGHT(text,LEN(text)-2)Formula
=RIGHT(A2,LEN(A2)-2)Usage Guide
Step_1: Type the formula in cell B2.
Step_2: Press ENTER.
Formula Explanation
- A2 is the text argument in the formula. It is the cell address where I am going to apply the formula.
- LEN(A2)-2 means the 2 characters are subtracted from the total length of the cell.
Step_3: Now, drag down the Fill Handle icon to copy the formula from cell B2 to cell B10.
Final Result >
This way, you can customize the formula to trim characters from the right with the RIGHT function or from the left side with the LEFT function.
Alternative Way #3: Use MID & LEN Function to Remove the First 2 Characters in Excel
With the MID function, it is possible to retrieve a portion of the string from a provided text string, by indicating the starting position and length of the string. And again, to count the number of characters in the string, I am going to use the LEN function.
Syntax
=MID(text, start_num, LEN(text)-2)Formula
=MID(A2,3,LEN(A2)-2)Usage Guide
Step_1: At first, write the formula in cell B2.
Formula Explanation
- A2 is the text argument in the formula. It is the cell address where I am going to apply the formula.
- 3 is the start_num. It indicates the starting number from where the characters will be returned in the result.
- LEN(A2)-2 is the char_num. It mentions that all the characters will return in the result except the first 2 characters from the left side.
Step_2: Next, hit the ENTER key.
Step_3: Now, drag the Fill Handle to copy the formula in the cell range B2:B10.
Final Result >
The MID function worked fine to remove the first 2 characters from the target cell range.
- 7+ Methods to Remove Characters from Left in Excel
- 5 Approaches to Remove Characters from the Right in Excel
- 3+ Ways to Remove Dashes in Excel
Alternative Way #4: Use Text to Columns Feature to Remove the First 2 Characters in Excel
The Text to Columns feature is a great tool that can separate the text string within the columns. In this technique, I will use the Text to Columns tool to separate the first 2 characters from the selected cell range.
Usage Guide
Step_1: At first, select the cell range A2:A10.
Step_2: Next, go to the Data tab.
Step_3: Now, click on the Text to Column icon from the Data Tools group.
This will bring up the Convert Text to Columns Wizard on the Excel sheet.
Step_4: Now, select the option Fixed Width.
Step_5: Hit Next.
Step_6: After that, inside the Data Preview box, position the cursor between the second and third characters and give a right-click.
It will create a barrier between the first 2 characters and the rest of the characters.
Step_7: Then click on the Finish button.
You will see the cells of the Sample Dataset are separated into 2 columns now, column A and column B. As a result, the first 2 characters are in column A, and the rest are in column B.
To clean up the column of the first 2 characters, follow these steps.
Step_8: Select the cell range A2:A10.
Step_9: Now, go to the Home tab from the ribbon.
Step_10: Then click on the Delete drop-down menu from the Cells group.
Step_11: Select Delete cells.
The Delete dialog box will pop up on the screen.
Step_12: Select the option Shift cells left.
Step_13: Finally click on the OK button.
Final Result >
The results are placed inside the Sample Dataset column. If you want you can keep them in a separate column.
Alternative Way #5: Use the Flash Fill Feature to Remove the First 2 Characters in Excel
The Flash Fill is a very smart tool. It readily catches the pattern of the first cell and fills up the column following the top cell. I am going to use the Flash Fill feature to show you this last approach.
Usage Guide
Step_1: At first, manually write down the content of cell A2 as it is, except the first 2 characters.
Step_2: Secondly, go to the Data tab.
Step_3: Next click on the Flash Fill icon from the Data Tools group.
Final Result >
The Flash Fill instantly understood that I want to receive the cells without the first 2 characters inside.
Conclusion
I hope this article helped you find what you were looking for and that you succeeded in removing the first two characters from the cells in Excel. So, please visit our page more often. Also if you want to know more about removing characters, click here. Lastly, let us know how you liked our blogs. Thanks for reading!
Related Articles
![[2 Cases] Count Columns in Excel Until a Certain Value Reached](https://excelgraduate.com/wp-content/uploads/2022/06/excel-count-columns-until-value-reached-3.png)
