Colleague and Benefactor Tips

Tip # 3

Presented by the
Central Information Services Team
cis@hamilton.edu
Ext: 5247

Adding Leading Zeros to Datatel-Generated EXCEL Spreadsheets

Have you ever used the EXCEL command in Colleague or Benefactor and been frustrated when it drops "leading zeros" from columns of numbers, such as Colleague or Benefactor ID's? In other words, instead of a seven digit Colleague or Benefactor ID like 0145672, you'd see one like 145672 (Note that the "leading zero" has been dropped). Luckily, there's a simple fix for this problem. Once you generate your spreadsheet, follow these steps:

1. Highlight the column of numbers to be fixed by moving your curser over the column letter at the very top of the column. For example, if you want to highlight the first column on the left, you'd move your curser over the letter "A" at the top of that column. As soon as you do this, you'll see a small downward-facing arrow appear. Click once on the column to highlight it. When the column is highlighted, it will turn blue.
 
2. From the Format dropdown menu, choose Cells (first choice at the top).

3. In the Format Cells window, choose the Number tab (first on the left, along the top – should already be the default choice).

4. Under the word "Category:" (on the left), there is a dropdown list of choices. Choose Custom (last choice at the bottom).

5. Beneath the word "Type:" (on the right), there is a single-line text box. Directly beneath that is a dropdown list of various choices. The single-line text box will initially have the word "General" in it. Note that the word "General" is also the first item listed in the dropdown list. Double-click on the single-line text box to highlight it. Use your backspace key to remove the word "General" from the single-line text box.

6. If the numbers you are trying to fix are, for example, Colleague or Benefactor ID's, then they all need to uniformly consist of seven digits. Many of the older Colleague and Benefactor ID's on our system contain one or more "leading zeros," as shown in the example above. So in this example, enter 0000000 (seven zeros) into the single-line text box (where you previously removed the word "General").

7. Click the OK button at the bottom of the Format Cells window. All of your Colleague or Benefactor ID's will now have seven digits, with leading zeros where appropriate.

8. Be sure to save your spreadsheet.



 

Key Resources

Services (phone numbers)