Free text in a coded field
A column meant to hold one of a few fixed values also holds comments, explanations, and whole sentences.
What it looks like
Before
| priority | priority_note |
|---|---|
| High | · |
| high, call first | · |
| Medium (per Dana) | · |
| see email | · |
After
| priority | priority_note |
|---|---|
| High | · |
| High | call first |
| Medium | per Dana |
| needs a decision | see email |
How it happens
- The field accepted any text, and people used it to explain the value as well as record it.
- There was no notes column, so the nearest field became one.
- None of the allowed values fit the case, so someone described it instead.
What it breaks
- Each annotated value counts as its own category, so group totals fragment.
- Filters on the fixed values miss every row that carries a comment.
- The comments, which often matter, are invisible to anyone looking only at the standard values.
How to spot it
- Count distinct values and compare with the number of allowed ones.
- Sort by length. Annotated cells are far longer than a bare code.
- Look for punctuation that a code would not have: commas, parentheses, and question marks.
How to fix it
- Separate the code from the comment: keep the allowed value in the column and move the rest to a note column.
- Where the cell names no allowed value at all, leave the code for a person to decide. The comment alone is not an answer.
- If the same comment keeps recurring, it may be a missing category. Adding it to the list is a decision for whoever owns the list.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Multiple values in one cellA single cell holds a list, or two different facts, joined by a comma, slash, or line break.
- Codes without a lookupA column holds short codes such as 3, A, or XR, and the table that says what each code means is not in the file.
TermsFree text, Controlled vocabulary, Categorical data, Validation