With Ultimate Suite for Excel

Add or remove leading zeros in 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 on your ribbon, click Utilities to proceed:
Click Utilities first.

How to add leading zeros

  1. 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:
    Selecting your range

    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.
  2. Set the maximum number of characters for your cells, including leading zeros, in the Number of characters field:
    Setting the maximum number of characters

    Tip.Click the Get Max Length button to make the length of the resulting strings equal to the longest string in the selected range.
  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 and the add-in will do all the work behind the scenes:
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.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

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.