Add or remove leading zeros in Excel
With Ultimate Suite for Excel
Easily change your cell content with the Add/Remove Leading Zeros add-in. If leading zeros are missing, make them appear. If they are no longer needed, eliminate them.
Before you start
All values to be processed should be of the same type since the tool adds leading zeros by differentiating between numeric and text values. It either changes the number format or modifies text strings.
Running the tool
To start the tool, click its icon in the Utilities group on the Ablebits Tools tab:
If the Utilities group is minimized and you cannot see the Add/Remove Leading Zeros icon on your ribbon, click Utilities to proceed:
How to add leading zeros
- Select the range of interest in the Select your range field. You can select the range right in the worksheet and the add-in will pick your selection, type in the range address manually, or highlight the entire table by clicking the Expand selection icon:
Note. To save your original data, opt for Back up this worksheet. We recommend keeping this option selected as Excel doesn’t let you undo changes made by add-ins. - Set the maximum number of characters for your cells, including leading zeros, in the Number of characters field:
Tip.Click the Get Max Length button to make the length of the resulting strings equal to the longest string in the selected range. - Click Apply.
This is the result:
If the maximum number of characters should be kept as is, without any changes, and you only need to add leading zeros to shorter sets, just click Apply and the add-in will do all the work behind the scenes:
This is the result:
How to remove leading zeros
- Select the range of interest:
Note. To save your original data, opt for Back up this worksheet. - Decide how many leading zeros you need and decrease the maximum number of characters in the box in the add-in window accordingly:
- Click Apply.
This is the result:
