Function Problems

How to Remove the First Digit from a Number in Excel

Khadija Khan Sep 28, 2026 869 views

Question details

The user needs to remove the first digit from an 11-digit numerical value, specifically handling numbers that begin with a zero, while avoiding the #NAME? error.

How to Remove the First Digit from a Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Modifying long numeric strings (such as 11-digit phone numbers or IDs) to remove the leading digit, ensuring zeros are kept where necessary.
Observed behavior: When stored as numbers, Excel automatically drops leading zeros, and incorrect formula implementations can trigger a #NAME? formula error.

Before you start

Ensure that your column of 11-digit values is formatted as Text before applying any formulas, so that Excel correctly preserves any leading zeros instead of automatically deleting them.

Solution 1Recommended

Use the MID Function to Extract Remaining Digits

The MID function is the most reliable way to skip the first character and pull the rest of the string, keeping leading zeros intact if stored as text.

By utilizing the MID function along with the LEN function, you can tell Excel to start extracting data from the second character and continue until the end of the string. This works dynamically for strings of any length.

Select the destination cell

Click on an empty cell adjacent to the number you want to modify (for example, B1 if your data is in A1).

Enter the MID formula

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

Apply the formula

Press Enter to see the result, then drag the fill handle down to apply this formula to the rest of the column.

Use the MID Function to Extract Remaining Digits

Formatting Tip: To prevent Excel from dropping initial zeros, verify that cell A1 is formatted as Text or type an apostrophe (') before entering the 11-digit number.

Solution 2

Use the RIGHT Function to Remove the First Character

The RIGHT function provides an alternative method by extracting all characters starting from the right side, minus the length of the first digit.

Advanced Spreadsheet Editor

Effortlessly Process Text and Numbers with WPS Spreadsheet

WPS Spreadsheet includes all the powerful text manipulation formulas you need, such as MID, RIGHT, and LEN. It allows you to clean and format vast amounts of data seamlessly and efficiently without compatibility issues.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the 11-digit numbers.
2. Format data as Text: Select your column, right-click, choose 'Format Cells', and set the category to Text to preserve leading zeros.
3. Apply the extraction formula: In an adjacent cell, enter =MID(A1, 2, LEN(A1)) and press Enter.
4. Fill the series: Drag the small square handle at the bottom right of the cell to easily apply the formula to all your data.

Fully compatible with Microsoft Excel (.xlsx) files and formulasBuilt-in robust text functions for effortless data extraction and manipulationLightweight application with rapid launch and smooth performanceFree and intuitive interface suitable for beginners and pros alike

Frequently Asked Questions

Why does Excel automatically remove the leading zero from my 11-digit number?

By default, Excel classifies any input made entirely of numbers as a numerical value. Mathematically, leading zeros hold no value, so Excel drops them. To retain the zero, you must format the cells as Text before typing the number, or place a single apostrophe (') at the beginning of your input.

What causes the #NAME? error when I use these formulas?

The #NAME? error generally occurs if a function name is misspelled or if a cell reference is typed incorrectly. Ensure that functions like MID, RIGHT, and LEN are spelled exactly as shown, and double-check that your cell references (like A1) actually exist.

Is there a way to use the REPLACE function to remove the first digit?

Yes. You can use the formula =REPLACE(A1, 1, 1, ""). This function tells Excel to start at the first character, replace exactly one character, and substitute it with an empty string, effectively removing the first digit.

Excel & Data Problems