Microsoft Excel offers a bevy of cell formatting options. Users can choose Currency or Accounting for monetary values, select a variety of date and time formats, use special formats for values such as ZIP codes and Social Security numbers, and even create custom formatting options. While these choices give Excel users a high degree of flexibility and are usually a plus, they can cause problems, particularly if you support users who routinely export data from Excel into applications that are less format-flexible.
Such is the case for TechRepublic member Rick Kuhn. Rick’s organization routinely exports data—names, addresses, dates, ZIP codes, Social Security numbers, and the like—from Excel into an in-house application that can accept dates only as text values in mmddyyyy format. Unfortunately, converting values from a date format to text in Excel isn’t as easy as clicking Format | Cells and changing the category. To make matters worse, many of the original Excel files have Social Security numbers and ZIP codes formatted as General instead of Text and are therefore missing leading zeros.
To help his Excel users quickly reformat spreadsheets into a form that their in-house software can use, Rick created three VB scripts: one that converts date formats into mmddyyyy, one that adds leading zeros where necessary, and a final script that performs a Copy, Paste, and Paste Special Values operation. To make the process as simple for his users as possible, Rick then created an Excel add-in, an Excel startup file, and three macros (i.e., hotkeys for the VB scripts). Rick’s Excel users now have a simple two-button fix for each problem.
To help you do the same for your Excel users, we’ve taken Rick’s files and combined them into a single download. Whether you’re trying to resolve the same problem as Rick, planning to customize his scripts for your users’ formatting needs, or just trying to learn more about Excel macros and scripting, our Excel data formatting macros download can help.
All the files you need in one download
This download contains all the components you need to provide a similar solution for your Excel users, including:
- Detailed introduction document in Word and Adobe PDF formats.
- Code for each VB script.
- Detailed instructions on how to install add-in and startup files.
- Detailed instructions on how to use the macros with the sample spreadsheet (Demo.xls).
- The Excel startup file (Personal.xls).
- The Excel add-in (Formatdates.xla).
- A sample spreadsheet (Demo.xls) with generic data that you can test.
Once you unzip the download file, ExcelFormat.zip, open Excel_format.doc and follow the instructions. In no time, you’ll have added three new buttons to your Excel toolbar, assigned macros to the buttons, and tested the buttons on the sample spreadsheet, Demo.xls. Figure A shows the three new buttons (Format Dates, Leading Zeros, and CPSV) in the top right-hand corner of the toolbar and gives you an example of what the buttons can do in columns B and D.
Download our Excel data formatting macros
You can download our Excel data formatting macros by following this link or by clicking on the Downloads link in the navigation bar at the top of this page. TechRepublic has many useful documents, templates, and applications available for download, so be sure to check out our other offerings.
Our Excel data formatting macros download contains four separate files that have been zipped together to increase download speed. You'll need an unzip utility such as WinZip or PKZIP to expand the zipped file. You'll also need either Microsoft Word or Adobe Acrobat Reader to read the download document and Microsoft Excel to use the macros, add-in, and startup file. You can download Adobe Acrobat Reader here.
Bill Detwiler has nothing to disclose. He doesn't hold investments in the technology companies he covers.
Bill Detwiler is Managing Editor of TechRepublic and Tech Pro Research and the host of Cracking Open, CNET and TechRepublic's popular online show. Prior to joining TechRepublic in 2000, Bill was an IT manager, database administrator, and desktop support specialist in the social research and energy industries. He has bachelor's and master's degrees from the University of Louisville, where he has also lectured on computer crime and crime prevention.