Wednesday, March 21, 2012

Zeros in Excel 2007 - How to

I often have lists of file path names in Excel 2007 and I want to eliminate all the text except for the file name. The file names I deal with are eight digit barcode numbers, often with leading zeros. Unfortunately when I eliminate the text, Excel gets rid of the leading zeros. 

Here's on way to preserve leading zeros in Excel 2007:

Here is a list of file path names.

To get rid of text, under the data tab, select Data To Columns.

Shown here, delimit to a fixed width of 8 characters.

This eliminates the leading zeros.

Select the column of numbers, and under the Home tab/General click the small arrow next to Numbers.

This opens the Format Cells dialog. Under Category, select Custom. Under Type: type in zeros for as many digits as you want you string of numbers to be, in this case, eight digits so eight zeros.Press OK.

The leading zeros are shown.

More flowers to follow.

No comments:

Post a Comment