Function Problems

Excel Formula to Remove the First Two Characters from a Cell

Phi Hung Vo Oct 16, 2026 870 views

Question details

The user needs an Excel formula to strip the first two leading letters or characters from a cell and retain the remaining variable-length string as a numeric value.

How to Remove the First Two Characters from a Cell in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning up data by removing leading letters, prefixes, or specific characters from alphanumeric strings in a worksheet.
Observed behavior: The user requires a solution that successfully removes exactly two leading characters and evaluates the remaining numerical characters as a true number.

Before you start

Ensure your target cells do not contain any accidental leading spaces that might be counted as characters, and confirm that the remaining text actually consists of numbers if you intend to use the result in calculations.

Solution 1Recommended

Use a Combination of RIGHT and LEN Functions

This dynamic method calculates the total length of the cell and extracts all characters except the first two from the right side.

The RIGHT function extracts a specific number of characters from the end of a string, while the LEN function determines the total length. Using them together makes the formula dynamic for variable-length strings.

Select the target cell

Click on an empty cell where you want the cleaned number to appear (for example, cell B1).

Enter the formula

Type the formula =--RIGHT(A1, LEN(A1)-2) into the formula bar.

Apply and convert

Press Enter. The double unary operator (--) at the beginning automatically converts the resulting text string into a numeric value.

Use a Combination of RIGHT and LEN Functions

Understanding the Double Unary Operator: The double dashes (--) force Excel to convert numeric text back into an actual number format, allowing you to use it in mathematical operations.

Solution 2

Use the REPLACE Function

A highly straightforward alternative that substitutes the first two characters with an empty value.

Solution 3

Use the MID Function

Extract characters starting from the third position using a sufficiently large number to capture the rest of the string.

Efficient Data Cleaning

Use WPS Spreadsheet to Easily Clean and Format Data

WPS Spreadsheet fully supports advanced text functions like RIGHT, LEN, REPLACE, and MID. You can seamlessly process large datasets, remove specific characters, and convert text to numbers.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing the alphanumeric data.
2. Apply the text formula: Select a target cell next to your data and enter =--RIGHT(A1, LEN(A1)-2).
3. Fill down the column: Hover over the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of the column.

Fully compatible with Microsoft Excel formulas and formats (.xlsx, .xls, .csv).Lightweight software that processes large data accurately and efficiently.Intuitive interface familiar to Office users, ensuring a smooth transition.

microsoft office alternative - wps office

Frequently Asked Questions

Can I remove characters from the end of a cell instead of the beginning?

Yes, you can achieve this by combining the LEFT and LEN functions. For example, the formula =LEFT(A1, LEN(A1)-2) will remove the last two characters of the string in cell A1.

Why do I get a #VALUE! error when using the double unary (--) operator?

This error occurs if the remaining characters after removing the first two are not purely numerical (e.g., they still contain letters or special symbols). The double unary operator can only convert clean numeric text into numbers.

How do I remove the first two characters without converting them to numbers?

Simply omit the double dashes (--) at the beginning of the formula. For example, use =RIGHT(A1, LEN(A1)-2) or =REPLACE(A1,1,2,""), and the result will remain in standard text format.

Excel & Data Problems