Have you ever wondered how to keep leading zeros in Excel when you publish your Chris21 reports? This is a very common problem, especially if it happens to be your employee numbers that have the leading zeros. For instance, if your employee number is 001234 and you run a report and publish to Excel, Excel will assume the number is 1234 and will drop the two leading zeros.

In most cases you will want to keep leading zeros so that the correct employee numbers appear in your reports. To do this you must choose the correct option when publishing Chris21 reports to Excel.

Run a report from Chris21 that has Employee Number as one of the report fields. From the main menu in the Report Designer, click Publish -> Spreadsheet. The following dialog box will appear:

By default the As text option will be selected. If you choose OK at this point your leading zeros will be removed. Instead, choose the second option Using the columns wizard and then click OK.

Leave the file type as Delimited and click Next.

Notice above that the report columns have now been defined. You don’t have to do anything here, just click Next.

The dialog box shown above is where we set the report to keep leading zeros in Excel. Click the column that stores the Employee Number. In this case it is the first column and is already highlighted. Now in the Column data format section click Text. This will format the employee number columns to be text rather than numeric. This effectively ensures that Excel recognises all the data, including leading zeros. If there are any other columns where there may be leading zeros, click those and set them as Text also. Now click Finish.

Your report has now been published to Excel and you will notice that the leading zeros on the Employee Number have been retained.

It’s a good idea to use this approach whenever you are publishing Chris21 reports to Excel. There may be other columns on your report that will need to be converted to text so you can move through each column and decide which ones need to be changed.

I hope this tip helps. Do you have a different method for doing this? If so, please let us know by leaving a comment.