How to keep or delete leading zeros in Excel?
When working with Excel, you may notice that entering a number with leading zeros—such as 00032423—will often result in the cell displaying only 32423. Excel automatically removes the leading zeros from numbers because they have no mathematical value. However, in many real-world applications such as employee IDs, product codes, or formatted data imports, it's important to retain or sometimes remove those leading zeros for consistency or compliance. This tutorial demonstrates several practical ways to keep or delete leading zeros in Excel, suitable for various scenarios, and the pros and cons of each method.
- Format cells to keep leading zeros
- Use the TEXT function to add leading zeros
- Add leading zeros automatically with VBA
- Quickly add leading zeros using Kutools for Excel
- Convert text to numbers to remove leading zeros
- Remove leading zeros with formula
- Delete leading zeros automatically with VBA
- One-click removal using Kutools for Excel
How to keep or add leading zeros in Excel
If your data requires keeping or adding leading zeros—for example, in codes, IDs, or formatted lists—Excel provides several flexible methods to achieve this. You can preserve leading zeros through cell formatting, formulas, or VBA, depending on whether your data needs to remain numeric or text-based.
In this section, we’ll explore multiple ways to keep or add leading zeros in Excel, ranging from built-in tools to advanced automation with Kutools for Excel, so you can choose the method that best fits your workflow.
Format Cells to Keep Leading Zeros
To preserve leading zeros in Excel, especially for codes, IDs, or other non-numeric identifiers, you should set the cell format to Text before entering any numbers. This prevents Excel from interpreting the input as a number and dropping the zeros.
1. Select the range of cells where you plan to enter data with leading zeros. Right-click the selection and choose Format Cells from the context menu. This opens the formatting options. See screenshot:
2. In the Format Cells dialog, navigate to the Number tab, and select Text from the Category list. Click OK to confirm and close the dialog. See screenshot:
Now, you can type numbers with leading zeros into these cells and Excel will display them exactly as typed, without dropping any zeros. See screenshot:
Note: If you format cells as Text after entering numbers, Excel will not automatically restore the leading zeros which have already been lost. Enter or paste your data after switching to Text format to guarantee the result.
This method is ideal for when you need to ensure all data is intentionally typed or pasted as text, such as when importing codes or identifiers from third-party sources.
Pros: Very simple and no formulas required; ensures exact text match.
Cons: Not suitable for bulk numeric conversion; must be set before data entry.
Use the TEXT Function to Add Leading Zeros
In some cases, you may have numerical data and want to display it with a fixed number of digits including leading zeros. The TEXT function allows you to convert numbers to text and apply custom formatting, such as adding zeros at the beginning.
For example, if your value in cell A1 is 32423, but you want the displayed result to always be8 digits: 00032423.
1. Enter the following formula in another cell (e.g., B1):
=TEXT(A1,"00000000")2. Press Enter to confirm. The formula cell will show the original value with the specified number of leading zeros added.
If you need to apply this to multiple rows, copy the formula cell down (drag the fill handle or use Ctrl+C/Ctrl+V) and adjust the references as needed.
Parameter Explanation: The number of zeros in "00000000" represents the total desired length for the number. Adjust as needed for your formatting needs.
Tips: If you need non-numeric character padding (e.g., A001), adjust the formatting string accordingly (e.g., "A000").
Pros: Easily apply fixed-length formatting; formula can be copied to large ranges.
Cons: Result is text format, not suitable for subsequent arithmetic operations.
Add leading zeros automatically with VBA
If you need to pad existing values with leading zeros so that each entry reaches a fixed length (such as for part numbers or consistent codes), you can use a VBA solution to automate this process. This method is especially helpful when data is inconsistently formatted or spread over a large range.
1. Go to Developer Tools > Visual Basic or press Alt + F11 to open the VBA editor. In the window, choose Insert > Module and paste the following code:
Sub AddLeadingZeros()
'Updated by Extendoffice
Dim WorkRng As Range
Dim xCell As Range
Dim strLen As Integer
Dim strZeros As String
Dim fixedLen As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range to pad with leading zeros", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
fixedLen = Application.InputBox("How many digits should each entry be?", xTitleId, "", Type:=1)
If fixedLen < 1 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In WorkRng
If Not IsEmpty(xCell.Value) And IsNumeric(xCell.Value) Then
strLen = Len(xCell.Value)
If strLen < fixedLen Then
strZeros = String(fixedLen - strLen, "0")
xCell.NumberFormat = "@"
xCell.Value = strZeros & CStr(xCell.Value)
End If
End If
Next
Application.ScreenUpdating = True
MsgBox "Leading zeros added successfully!", vbInformation, xTitleId
End Sub2. After entering the code, press the 
button. A dialog box prompts you to select the target cell range, then to specify the desired fixed length.
The macro will automatically add leading zeros to each cell so every entry matches the chosen length.
Precautions: This script overwrites cell values. Consider backing up your worksheet before running for large or sensitive datasets.
Pros: Fast bulk processing; supports any fixed length you specify.
Cons: Requires macro permissions and some experience with VBA.
Quickly add leading zeros using Kutools for Excel
Add/Delete leading zeros with Kutools for Excel
To quickly add the same count of leading zeros to cells (for formatting codes or identifiers), Kutools for Excel offers an Add Text tool. This utility provides preview and batch processing to ensure accuracy when editing formatted data.（Free Download Kutools for Excel Now!)
1. First, you need to format the target cells as Text. Select them, click Home, go to the Numeric group and pick Text from the dropdown list.
2. With the cells still selected, click Kutools > Text > Add Text.
3. In the Add Text dialog, enter the number of zeros to add, and check Before first character. Preview the result in the right pane.
4. Click OK or Apply to add leading zeros to all selected cells.
This is helpful for batch formatting IDs and cataloging numbers.
Delete leading zeros
In some cases, your dataset may contain unwanted leading zeros—often the result of imported text files, system exports, or inconsistent data entry. For example, values like 00045 or 00789 may need to appear as 45 or 789 for calculation or analysis purposes.
Fortunately, Excel offers several quick ways to remove these unnecessary zeros. Whether you prefer using built-in tools, formulas, VBA code, or Kutools for Excel, you can easily clean up your data and convert text strings into proper numeric values.
Convert text to numbers to remove leading zeros
Where numeric values are stored as text, Excel shows a warning icon beside such cells. To instantly convert and remove leading zeros, select the range, click the warning icon, and pick Convert to Number from the menu. See screenshot:
Then, the numbers stored as text are converted to numeric values while removing the leading zeros.
Remove leading zeros with formula
To convert numbers stored as text with leading zeros back to numeric values, choose a blank cell adjacent to your list (such as B1). Enter:
=VALUE(A1)After pressing Enter, the formula cell will show the original number with leading zeros automatically removed. Drag down to apply the formula to additional cells as needed.
This approach is very practical when you want to quickly normalize imported text data or convert numeric strings for calculations.
Pros: Fast and easy; works well for bulk conversion.
Cons: Results become real numbers; not suitable if you need text codes preserved.
If you know exactly how many leading zeros or characters to remove, you can use the RIGHT function to extract the desired digits from a text string. This approach is helpful when the data follows a fixed format or when you want to retain only a certain number of characters from the end.
1. Suppose cell A1 contains "00032423" and you want to keep only the5 trailing digits. Enter this formula in a blank cell (e.g., B1):
=RIGHT(A1,5)After pressing Enter, your formula will display "32423". Adjust the number argument to specify how many final characters to retain. If you know there are always3 leading zeros to remove, you can also use:
=RIGHT(A1,LEN(A1)-3)This formula removes the first3 characters regardless of their value. Copy the formula down if needed for more cells.
Error Reminders: If different rows have variable numbers of leading zeros, this approach may need adjustment. For inconsistent data, consider combining with VALUE or more advanced logic.
Pros: Flexible for fixed-format removal and non-numeric code handling.
Cons: Manual adjustment required for variable-length data.
Delete leading zeros automatically with VBA
For advanced users, VBA delivers extra flexibility for custom batch removal. To use VBA:
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the VBA window, click Insert > Module, then copy and paste the provided code:
VBA: Delete leading zeros in Excel
Sub DeleteZero()
'updateby20140616
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
WorkRng.NumberFormat = "General"
WorkRng.Value = WorkRng.Value
End Sub
3. Click the Run button. A KutoolsforExcel dialog will appear for you to select the target range of cells for zero removal. See screenshot:
4. Click OK and the leading zeros in your selection will be instantly deleted.
This solution is useful for automating repetitive tasks, customizing logic, or applying rules selectively to large datasets.
Pros: Highly customizable, efficient for complex needs.
Cons: Requires basic VBA knowledge and permission to run macros.
One-click removal using Kutools for Excel
With Kutools for Excel installed, you can instantly remove all leading zeros from multiple cells in just a few clicks using its Remove Leading Zeros utility.（Free Download Kutools for Excel Now!)
Simply select the cells where you wish to remove zeros, then click Kutools > Text > Remove Leading Zeros. See screenshot:
A dialog will confirm how many cells were changed. 
Click OK to finish and close the dialog.
This method is useful for clearing leading zeros from large ranges without formula complications—no extra set up required.
Throughout any method, pay attention to data type handling. Converting between numbers and text may impact formulas or downstream calculations. When in doubt, always backup your data before applying bulk format operations, whether by formula, VBA, or Kutools utilities.
If you encounter unexpected results, check for mixed cell formats or inconsistent input types. For formula solutions, ensure your references use the correct cell addresses and that output cells are formatted to display results as intended.
For additional tasks involving complex custom formatting or repetitive batch edits, consider combining VBA and Kutools tools for even more efficient workflows.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in