5 Ways To Remove First 2 Characters In Excel

Removing the first two characters from a cell in Excel can be a common task, especially when dealing with data that has been imported from other sources. This can be achieved through various methods, including using formulas, find and replace, and text functions. Below are five ways to remove the first two characters in Excel, catering to different user needs and comfort levels with Excel functions.

1. Using the RIGHT Function

The RIGHT function in Excel is useful for extracting a specified number of characters from the right side of a text string. To remove the first two characters, you can use this function to extract all characters except the first two.

How to Use the RIGHT Function:

• Assume the text from which you want to remove the first two characters is in cell A1.
• The formula would be: =RIGHT(A1, LEN(A1)-2)
• The LEN function calculates the total length of the text in A1, and by subtracting 2, you effectively get all characters except the first two.

2. Using the MID Function

The MID function extracts a specified number of characters from a text string, starting at a specified position. While it's more straightforward for extracting middle characters, it can also be adapted to remove the first two characters by extracting all characters from the third position onwards.

How to Use the MID Function:

• Again, with the text in cell A1, the formula would be: =MID(A1, 3, LEN(A1)-2)
• The MID function starts extracting from the third character (3), and extracts LEN(A1)-2 characters, thus excluding the first two.

3. Using VBA Macro

For users comfortable with VBA (Visual Basic for Applications), creating a macro can be a quick and reusable solution. This method is particularly useful if you frequently need to perform this task.

Creating a VBA Macro:

1. Press Alt + F11 to open the VBA Editor.
2. Insert a new module (Insert > Module).
3. Paste the following code: Sub RemoveFirstTwoCharacters() For Each cell In Selection cell.Value = Right(cell.Value, Len(cell.Value) - 2) Next cell End Sub
4. Save the module.
5. Select the cells you want to apply this to and run the macro (Developer > Macros, select RemoveFirstTwoCharacters, and click Run).

4. Using Find and Replace

While not a function, the Find and Replace feature in Excel can be used in a way that seems to remove the first two characters, especially if those characters are the same across the range you're working with.

How to Use Find and Replace:

1. Select the range of cells.
2. Press Ctrl + H to open Find and Replace.
3. In the Find what field, enter the first two characters.
4. Leave the Replace with field blank.
5. Click Replace All.

5. Using Power Query

Power Query is a powerful data manipulation tool within Excel. It can be used to remove the first two characters from a text column.

How to Use Power Query:

1. Select the column containing the text.
2. Go to the Data tab, and click From Table/Range.
3. In the Power Query Editor, select the column you want to modify.
4. Go to the Add Column tab, and click Custom Column.
5. Use the formula: = Text.Middle([YourColumnName], 2, Text.Length([YourColumnName]) - 2)
6. Click OK, then Close & Load.

Excel Text Manipulation Gallery

Removing the first two characters in Excel can be a straightforward task, depending on the method you choose and your familiarity with Excel's functions and features. Whether you opt for a quick formula solution or leverage the power of VBA or Power Query, there's an approach suitable for everyone.