Hello, Excellers welcome to another Excel #formulafriday. Today I want to share with you a solution to a problem that is really quite common and I get asked about from time to time. How do you pad out your numbers in Excel with zeros?. For example, you may have an Excel worksheet that is imported into another database system. A field in the data requires numbers to be a specific length. For example customer numbers or references that need to be 10 characters in length. A solution to this if your customer numbers are currently too small or short, is to pad the numbers out with zeros. We will use the LEFT function or formula to extract the correct number of characters from a newly created text string.

Here is a sample of some data to demonstrate this issue.

All of my customer ID’s are currently 8 characters long, and my upload file expects 10. I just need to add two zeros to the end of the number to ensure they import into my database with no problems. I can do this by creating a new text string that joins my original Customer Id and 10 zeros, then force Excel to take the first 10 characters using the LEN function. Let’s walk through the formula.

How Does The Formula Work?

It is a clever formula. First, we join the value in Column A to a text string of 10 zeros to create a brand new text string.

We then use the LEFT function to extract the first 10 characters from this newly generated string.

Using The LEFT Function

The LEFT Function extracts a given number of characters from the left side of a string.