Adding Leading Zeroes (MS-Excel)

Let’s say that you are working with data values, and for whatever reasons, you need to add leading zeroes to a certain range of column data.

In Microsoft Excel, accomplishing this task is relatively easy.

Ex. 334 needs to become 000000334

First, you will need to find a cell destination, preferably in a new column on the same row as the current data observation variable.

Within that cell, enter the formula:

=TEXT(C23, “000000000”)

Replace C23 with the cell location of the original data. You can then drag this formula either vertically or horizontally to create modified new values which reference the original data entries.

The length of the consecutive zero string depends on the desired length of the variable output.

For example:

If C23 contained the value 334.

=TEXT(C23, “000000000”) would create a new value in the destination cell: 000000334

=TEXT(C23, “00000000”) would create a new value in the destination cell: 00000334

=TEXT(C23, “0000000”) would create a new value in the destination cell: 0000334

=TEXT(C23, “000000”) would create a new value in the destination cell: 000334

=TEXT(C23, “00000”) would create a new value in the destination cell: 00334

=TEXT(C23, “0000”) would create a new value in the destination cell: 0334

=TEXT(C23, “000”) would create a new value in the destination cell: 334

It is important to note that if you are planning on saving this file containing the data as a .CSV for a SAS import, that you will lose the formatting upon saving and importing.

The best way to work around this is to copy the new data column that contains the new formats, and paste it into an additional new column destination as values.