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.

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. Two characters precede each name. 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.

Remove the First 2 Characters in Excel Using VBA

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 Sub

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

Steps to remove the first 2 characters in Excel using VBA:

1. Press ALT + F11 to open the Visual Basic Editor.
2. Then select Insert > Module.
3. Then paste the code into the new module.
4. Click on the Run button or press the F5 key to run the code.

As I ran the VBA code, It instantly filled up the cell range B2:B10 with the output.

Remove the First 2 Characters in Excel Using REPLACE Function

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,””)

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

To use the REPLACE function to remove the first 2 characters in Excel:

1. Select cell B2.
2. Write the formula: =REPLACE(A2,1,2,””)
3. Press the ENTER key.
4. Now copy down the formula by dragging the Fill Handle icon from cell B2 to cell B10.

The REPLACE function has removed the first 2 characters successfully. Have a look:

• 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]

Remove First 2 Characters in Excel Using RIGHT & LEN Function

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)

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.

Steps to remove the first 2 characters in Excel using the RIGHT & LEN function:

1. Select cell B2.
2. Type the formula: =RIGHT(text,LEN(text)-2)
3. Press ENTER.
4. Now, drag down the Fill Handle icon to copy the formula from cell B2 to cell B10.

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.

Remove the First 2 Characters in Excel Using MID & LEN Functions

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)

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.

To remove the first two characters using MID & LEN functions, follow the steps below:

1. At first, select cell B2.
2. Type this formula: =MID(A2,3,LEN(A2)-2)
   
3. Next, hit the ENTER key.
4. Now, drag the Fill Handle to copy the formula in the cell range B2:B10.

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

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.

So, here are the steps to remove the first 2 characters in Excel with Text to Columns:

1. At first, select the cell range A2:A10.
2. Next, go to the Data tab.
   
   
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.
4. Now, select the option Fixed Width.
5. Hit Next.
   
   
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.
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.
8. Select the cell range A2:A10.
9. Now, go to the Home tab from the ribbon.
10. Then click on the Delete drop-down menu from the Cells group.
11. Select Delete cells.
    The Delete dialog box will pop up on the screen.
12. Select the option Shift cells left and click OK.

The results are placed inside the Sample Dataset column. If you want you can keep them in a separate column.

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.

Now, use the Flash Fill feature to remove the first 2 characters in Excel:

1. At first, manually write down the content of cell A2 as it is, except for the first 2 characters.
   
2. Secondly, go to the Data tab.
3. Next, click on the Flash Fill icon from the Data Tools group.

The Flash Fill instantly understood that I wanted 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!

Frequently Asked Questions

How do I remove second character in Excel?

To remove the second character in Excel, you can use the CONCATENATE function along with the MID function. Here’s a formula you can use: =CONCATENATE(LEFT(cell_reference, 1), MID(cell_reference, 3, LEN(cell_reference)-2))
Replace “cell_reference” with the reference to the cell containing the text. This formula concatenates the first character with the portion of the text starting from the third character, effectively removing the second character. Adjust the cell reference accordingly for your specific case.

How do I remove 3 digits from left in Excel?

To remove 3 digits from the left in Excel, use the RIGHT function along with LEN. Here’s a formula you can use: =RIGHT(cell_reference, LEN(cell_reference)-3)
Replace “cell_reference” with the reference to the cell containing the text. This formula extracts the portion of the text starting from the fourth character, effectively removing the first three digits. Adjust the cell reference accordingly for your specific case.

How do I cut off digits in Excel?

To cut off digits in Excel, use the LEFT or RIGHT function depending on whether you want to retain the leftmost or rightmost part of the number. Here’s an example to cut off the rightmost digits: =LEFT(cell_reference, LEN(cell_reference) – num_digits_to_cut)
Replace “cell_reference” with the reference to the cell containing the number and adjust “num_digits_to_cut” to specify the number of digits you want to cut off from the right. This formula retains the leftmost part of the number, effectively cutting off the specified digits. Adjust the cell reference and digit count as needed for your specific case.

Related Articles

• 3 Ways to Remove Specific Characters in Excel
• 5 Ways to Remove Blank Characters in Excel
• 5 Ways to Remove Numeric Characters from Cells in Excel
• 6 Ways to Remove Single Quotes in Excel
• 12 VBA Codes to Remove Characters From String in Excel