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:
Click the tool icon.
If the Utilities group is minimized and you cannot see the Add/Remove Leading Zeros icon in your ribbon, click Utilities to proceed:
Click Utilities first.

How to add leading zeros

  1. Select the range of interest:
    Selecting your range
    Pay attention to the icons on the right side of the Select your range box. The first icon is there to help you select your whole table. The second one can be used to get a different range selected.

    Note. To save your original data, opt for Back up this worksheet.
  2. Decide on the maximum number of characters, including leading zeros, the cells should contain and increase, if necessary, the number you already have to the desired one by changing digits in the Number of characters box in the add-in window:
    Setting the maximum number of characters
  3. Click Apply.

This is the result:
Here 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:
Keeping the maximum length as is
This is the result:
Here is the result.

How to remove leading zeros

  1. Select the range of interest:
    Selecting your range

    Note. To save your original data, opt for Back up this worksheet.
  2. Decide how many leading zeros you need and decrease the maximum number of characters in the box in the add-in window accordingly:
    Adjusting the string length
  3. Click Apply.

This is the result:
Here is the result.

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.