Table of Contents
Remove Leading Zeros
This guide teaches you how to remove leading zeros in Excel.
The first method uses Paste Special to remove leading zeros from numbers affected by text formatting. The second method uses a simple formula to strip leading zeros from text while preserving other zeros.
Remove Leading Zeros from Numbers
Leading zeros in numerical data can be removed using an empty cell and Paste Special in Excel. The cells below contain numbers with leading zeros.
Cell A2: Text format applied before entering a number with leading zeros.
Cell A3: Apostrophe used at the beginning to force text format.
Cell A4: Custom number format with leading zeros.
The following clever trick removes all these leading zeros at the same time!
1. Copy an empty cell (press Ctrl + C).
2. Select the cells that contain the numbers with leading zeros.
3. Right-click on the selected range and then choose “Paste Special”. You can also simply press Ctrl + Alt + V (paste special shortcut).
The Paste Special dialog box appears.
4. Select “Add” under “Operation” and press OK.
That’s it. This will remove all leading zeros from numbers. Read on to learn how to strip leading zeros from text.
How it works: using the “Add” operation in Paste Special changes how Excel handles the data format. Although it seems like adding an empty cell shouldn’t affect the data this triggers Excel to reevaluate the formatting of cells initially recognized as text and convert them to General format thereby removing any leading zeros.
Strip Leading Zeros from Text
Leading zeros in text can be removed using a simple Excel formula. The beauty of this formula is that it preserves all other zeros deleting only the leading zeros.
To remove the leading zeros from this text data execute the following steps.
1. Enter the formula =IF(LEFT(A2) = “0” RIGHT(A2 LEN(A2)-1) A2) in cell B2.
2. Copy the formula down to cover all relevant rows in column B. This removes the first leading zero from each cell.
3. Select the range B2:B5 and drag it across until the text data does not change anymore. Dragging a few extra columns won’t affect the final result as no further changes will occur once all leading zeros are removed.
How it works: the formula begins by checking if the first character of the string is a zero using the LEFT function. If this condition is true it uses the RIGHT function to extract all characters from the string except the first one. The total number of characters to extract is calculated by the LEN function which determines the length of the string and subtracts one.
This process is repeated across the columns peeling off one zero at a time if the new leading character is still a zero.
