Remove the Last Two Digits from a Number in Excel
Question details
Remove the final two digits from each numeric value while returning the remaining digits as a number.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to process values such as 2209, 15988, and 188 in a worksheet column and retain every digit except the last two.
- Observed behavior
- The required results are 22 from 2209, 159 from 15988, and 1 from 188.
Confirm that the source values are in one column and do not require leading zeros to be preserved. If leading zeros are meaningful, use the text-only formula rather than converting the result to a number.
Use LEFT and LEN to remove the final two digits
Use this formula when the values contain at least three digits and you want the result returned as a number.
LEFT extracts the characters before the final two positions, LEN counts the total characters, and the double unary converts the extracted text back to a numeric value.
Click the cell next to the first value, such as B2 when the original number is in A2.
Type =--LEFT(A2,LEN(A2)-2) in B2, then press Enter.
Select B2 and drag the fill handle down, or double-click the fill handle, to apply the formula to the remaining rows.
Handle values with fewer than three digits safely
Use this version when some cells may contain one- or two-digit values and should not produce an invalid extraction length.
Remove the Last Two Digits with a Formula in WPS Spreadsheets
WPS Spreadsheets supports the same LEFT, LEN, and IF functions, so you can remove the final two digits, convert the result to a number, and fill the formula through your worksheet quickly.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the number column.
- 2. Choose the first result cell: Click the cell beside your first source value, such as B2 for a value stored in A2.
- 3. Enter the digit-removal formula: Type =--LEFT(A2,LEN(A2)-2) in the formula bar and press Enter.
- 4. Apply the formula to the column: Drag the fill handle from the bottom-right corner of the result cell down through the required rows.
Frequently Asked Questions
Why does the formula use two minus signs before LEFT?
The double unary in =--LEFT(A2,LEN(A2)-2) converts the text returned by LEFT into a numeric value, allowing it to be used directly in calculations.
What formula should I use if my values can have only one or two digits?
Use =IF(LEN(A2)<=2,"",--LEFT(A2,LEN(A2)-2)). Enter it in the result cell beside A2, then fill it down. It returns a blank for values with two or fewer digits.
How can I keep the result as text instead of a number?
Remove the double unary and use =LEFT(A2,LEN(A2)-2). This is useful when the retained characters may include leading zeros.
Can I remove the last two digits from an entire Excel column at once?
Enter the formula in the first output cell, such as B2, then drag or double-click the fill handle to copy it down beside all populated values in column A.
Does this formula change the original number in column A?
No. The formula creates a result in a separate cell. To replace the original values, copy the results, use Home > Paste > Paste Values, and then overwrite the original column if needed.
