5 Easy Ways To Remove First Two Digits From Numbers In Excel

When working with large datasets in Excel, you may often find yourself needing to manipulate numbers for various purposes. One common scenario is wanting to remove the first two digits from a series of numbers. This can be particularly helpful if you have account numbers, codes, or other numerical data where only the trailing part is significant. Fortunately, Excel has several methods to accomplish this task easily and efficiently.

1. Using the RIGHT Function

The RIGHT function is a simple way to extract a specified number of characters from the right side of a text string. To remove the first two digits, you can use the following formula:

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

• A1 is the cell that contains the original number.
• LEN(A1) returns the total number of characters in the cell, and subtracting 2 gives you the number of characters to extract from the right.

2. Using Text to Columns

If you prefer a more visual method, Text to Columns is an excellent tool to split data based on a specific delimiter or fixed width.

Steps:

1. Select the range of cells containing the numbers.
2. Go to the Data tab and click on Text to Columns.
3. Choose Fixed width and click Next.
4. In the preview, click to set a line after the second digit and then click Next.
5. Choose the destination cell and click Finish.

The first two digits will be separated into a new column, leaving you with the desired numbers in your original column.

3. Using the MID Function

Another effective way to cut off unwanted characters is using the MID function. It allows you to specify where to start extracting characters.

=MID(A1, 3, LEN(A1) - 2)

• This formula tells Excel to start at the third character and go until the end of the string. It effectively omits the first two characters.

4. Using Flash Fill

If you're using Excel 2013 or later, the Flash Fill feature can be a game changer for this kind of task.

Steps:

1. In an adjacent column, manually type the desired result for the first entry.
2. Start typing the result for the second entry. Excel will automatically suggest the rest of the column.
3. Press Enter to accept the Flash Fill suggestion.

This method is intuitive and saves you from having to write formulas.

5. Using VBA (Macro)

For more advanced users, writing a simple VBA macro can automate the process, especially if you're dealing with large datasets.

Example Code:

Sub RemoveFirstTwoDigits()
    Dim cell As Range
    For Each cell In Selection
        If IsNumeric(cell.Value) Then
            cell.Value = Right(cell.Value, Len(cell.Value) - 2)
        End If
    Next cell
End Sub

• To use this macro, you need to enable the Developer tab, create a new module, and paste the code. Select the range of cells before running the macro.

Common Mistakes to Avoid

• Data Type Issues: Make sure the cells you’re working with are formatted as text or numbers. Excel sometimes misinterprets formats, which can lead to errors in your functions.
• Using Cell References Incorrectly: Ensure you’re referencing the right cells in your formulas. A simple typo can lead to unintended results.
• Exceeding String Length: If your numbers have fewer than two digits, these methods may return an error or unexpected results. Always check the length of your data first!

Troubleshooting Common Issues

• Formula Errors: If your formula returns #VALUE!, double-check that you’re working with text values when using functions that require string manipulation.
• Trailing Spaces: If your output appears incorrect, there might be trailing spaces in your data. Use the TRIM function to clean up the data before processing.
• VBA Macro Not Working: Make sure macros are enabled in your Excel settings. You might also need to adjust security settings to allow them to run.

<div class="faq-section"> <div class="faq-container"> <h2>Frequently Asked Questions</h2> <div class="faq-item"> <div class="faq-question"> <h3>Can I remove more than two digits?</h3> <span class="faq-toggle">+</span> </div> <div class="faq-answer"> <p>Yes, you can modify the formulas by changing the number you subtract in the LEN function or the starting point in the MID function.</p> </div> </div> <div class="faq-item"> <div class="faq-question"> <h3>What if the number has fewer than two digits?</h3> <span class="faq-toggle">+</span> </div> <div class="faq-answer"> <p>The formulas may return an error or blank if there are fewer than two digits. It's good to add error handling or check the length of the data first.</p> </div> </div> <div class="faq-item"> <div class="faq-question"> <h3>Can I use these methods on large datasets?</h3> <span class="faq-toggle">+</span> </div> <div class="faq-answer"> <p>Absolutely! However, if you're working with extensive datasets, using the VBA method can save a lot of time.</p> </div> </div> </div> </div>

By mastering these techniques, you can efficiently remove the first two digits from numbers in Excel and enhance your data manipulation skills. Whether you prefer formulas, VBA, or the user-friendly Flash Fill method, each option has its unique benefits and will suit different preferences and scenarios.

While working through these methods, remember to practice regularly to become more comfortable with Excel’s capabilities. Exploring related tutorials can also broaden your knowledge and boost your productivity.

<p class="pro-note">🌟Pro Tip: Practice these methods on a sample dataset first to understand how they work before applying them to your main data! </p>