Cleaning Roster Datafor Union Campaign Success
Module 4 · Placeholders, Junk, and Standardization › 4.3 Standardize names and ZIPs
Subsection 4.3

Standardize names and ZIPs

~6 min

Reading

Two of the most common standardization problems in union rosters are: name casing that is inconsistent (some ALL CAPS, some all lowercase, some Mixed) and ZIP codes that lost their leading zeros when the file was opened in Excel.

Both have one-line fixes.

PROPER for name casing

The PROPER function capitalizes the first letter of each word and lowercases the rest:

=PROPER(A2)

  • GLORIA SANCHEZ becomes Gloria Sanchez.
  • james o'neil becomes James O'Neil.
  • maria del rosario becomes Maria Del Rosario.

PROPER breaks some names

Names like McDonald become Mcdonald. Names like de la Cruz become De La Cruz. PROPER is a starting point, not a finish. Always check a sample of 20 rows by eye after running it.

TEXT to restore leading zeros on ZIPs

Excel and Sheets treat 03014 as the number 3014 and silently drop the leading zero. The fix is to wrap the column with TEXT and a format string:

=TEXT(F2, "00000")

The "00000" format string says “pad with leading zeros to exactly 5 digits.” Run this on every ZIP column in your roster before you do anything else.

Action: Mark this page complete when you have finished the activity above.