Converting Numeric Codes to Alpha Codes

Using Excel and Access

---

Do you want to join two files using district or school codes but there are numeric codes in number format without leading 0's in one file and numeric codes in text format with leading 0's in another? Follow these steps:

Add leading 0's using Excel.

1. Highlight the column/field.
2. Click on "format," then "cell," then "number," then "custom."
3. Type in 0000 in the box under the word "type."
4. Click on "OK."
5. Save the file (tab-delimited is OK).

Convert column/field containing numeric code from number to text format using Excel or Access.

(You will need to repeat this process every time you open or import the tab-delimited file. Excel and Access will read numeric codes in tab-delimited files as numbers by default unless you tell it otherwise. The person importing the file needs to specify that such columns need to be read as text.)

1. Begin the usual process to open/import the file in Excel or Access.
2. In "text import wizard" step 3 or 4, highlight the column you want to be text format.
3. Click on "text" under "column data format" or "data type."
4. Click on "finish" or "next."

You should now be able to join this file to other files containing the alpha codes with leading 0's.

For questions about this information, contact oeamail@dpi.wi.gov

Last updated on 2/26/2008 10:43:20 AM