How to Easily Remove the First Two Digits from Excel Cells

Often you may want to remove the first 2 digits from a cell in Excel to streamline your data analysis tasks.

You can use the RIGHT function combined with the LEN function to do so effectively across any range of data:

=RIGHT(A2,LEN(A2)-2)

This particular formula removes the first 2 digits from cell A2 by calculating the total length of the String and subtracting the prefix.

For example, if cell A2 contains AA4506 then this formula would return just 4506, effectively stripping the alpha characters and leaving only the numeric portion.

The following example shows how to use this formula in practice, illustrating the transformation of raw data into a clean, standardized format.

Example: Remove First 2 Digits from Cell in Excel

Suppose we have the following list of employee ID’s in Excel, where each ID is prefixed with a two-letter department code that is no longer required for our specific report:

Suppose we would like to remove the first two digits from each employee ID to isolate the unique identification number for a system migration.

We can type the following formula into cell B2 to do so, utilizing a relative reference so the formula can be easily scaled:

=RIGHT(A2,LEN(A2)-2)

We can then click and drag this formula down to each remaining cell in column B, allowing Excel to automatically update the cell references for each row:

Column B now displays the employee ID’s in column A with the first two digits removed from each employee ID, resulting in a clean list of numerical values.

Understanding the Formula Logic

The RIGHT function in Excel is designed to extract a specific number of characters starting from the far-right side of a text String. By itself, the function requires two arguments: the text source and the number of characters you wish to retain. However, when the goal is to remove a fixed number of characters from the beginning, the total number of characters to retain varies based on the length of each individual cell.

This is where the LEN function becomes indispensable; it calculates the total character count of a given cell. By nesting the LEN function within the RIGHT function and subtracting 2, you are essentially telling the software to determine the length of the entire string and then return everything except for the first two characters.

Thus, our formula tells Excel to extract the amount of characters equal to the length of the string minus two characters, providing a dynamic solution that works regardless of whether the original ID is five characters long or fifty characters long.

Note: Whitespace characters or blank spaces at the start of a string count as characters just like letters or numbers. If your data contains inconsistent spacing, you may need to first apply the TRIM function to remove blank spaces to get your desired result and ensure accuracy in your Data Cleaning process.

Alternative Method: Using the MID Function

While the combination of RIGHT and LEN is highly effective, another popular approach involves the MID function. The MID function is designed to return a specific number of characters from the middle of a text String, given a starting position and a length. To remove the first two digits, you would set the starting position to 3, effectively skipping the first two characters entirely.

The syntax for this method would be =MID(A2, 3, LEN(A2)). In this configuration, the formula starts at the third character and extracts a total number of characters equal to the length of the original string. Because the starting point is character three, Excel naturally stops once it reaches the end of the text, providing an identical result to the previous method without the need for subtraction.

Many advanced users prefer the MID function because it is slightly more intuitive for removing prefixes. It explicitly defines where the “good” data begins. Regardless of which function you choose, both methods are foundational to high-level Data Cleaning and ensure that your Spreadsheet remains professional and error-free.

Utilizing Flash Fill for Rapid Results

For users who prefer a more visual or automated approach without writing complex formulas, Excel offers a powerful feature known as Flash Fill. This tool uses pattern recognition technology to sense what you are trying to accomplish and completes the task for you across the entire dataset. To use this, you simply type the desired result for the first two cells in an adjacent column, and Excel will suggest a completion for the rest of the column.

Flash Fill is particularly useful for one-time tasks where you do not need the results to update dynamically if the source data changes. While formulas are better for live dashboards, this feature is a significant time-saver for quick data audits. It effectively identifies the removal of the first two digits as the intended pattern and applies that logic to thousands of rows in a matter of seconds.

To activate this feature, you can navigate to the Data tab on the ribbon and click the Flash Fill button, or simply press Ctrl+E on your keyboard. This shortcut is a favorite among data professionals who need to perform rapid Data Cleaning without getting bogged down in syntax.

Advanced Cleaning with Power Query

When dealing with massive datasets that exceed the standard row limits of a Spreadsheet, or when the data transformation needs to be part of a repeatable pipeline, Power Query is the superior choice. This engine allows you to perform advanced transformations, including removing a set number of leading characters, through a user-friendly graphical interface. Within the Power Query editor, you can select a column and use the “Transform” options to “Remove Characters” from the “Start” of the string.

The primary advantage of using Power Query is that every step you take is recorded. If you refresh your data source with new information next month, the tool will automatically apply the “Remove first 2 digits” step to the new data, ensuring consistency without any manual intervention. This is a cornerstone of modern business intelligence and data engineering workflows within the Excel ecosystem.

Furthermore, Power Query can handle complex logic that simple formulas might struggle with, such as removing digits only if they are numeric, or removing a variable number of characters based on a delimiter. For anyone looking to scale their data processing capabilities, mastering these tools is a logical next step after learning basic text functions.

Best Practices and Troubleshooting

When applying these techniques, it is crucial to keep a few best practices in mind to avoid common pitfalls. First, always work on a copy of your data or in a new column to ensure that you do not accidentally overwrite your original source values. Data loss can be catastrophic in financial or research settings, so maintaining a backup of the raw String data is highly recommended.

Secondly, be mindful of the data type in the resulting Cell. If you remove the first two digits from a string and the remaining characters are numbers, Excel might still treat the result as text. If you need to perform mathematical calculations on the result, you can wrap your formula in the VALUE function, like so: =VALUE(RIGHT(A2, LEN(A2)-2)). This converts the text string back into a numeric format that can be summed, averaged, or used in charts.

Finally, always check for hidden Whitespace. Non-printing characters can often sneak into datasets during exports from web-based tools. Using the CLEAN and TRIM functions in conjunction with your character removal formulas will ensure that you are targeting exactly the two digits you intended to remove, leading to a much higher success rate in your Data Cleaning efforts.

The following tutorials explain how to perform other common operations in Excel, helping you build a comprehensive toolkit for data management and analysis: