Multiple values in one cell
A single cell holds a list, or two different facts, joined by a comma, slash, or line break.
What it looks like
Before
| contact | tags | |
|---|---|---|
| Ana Ruiz / ana@example.com | · | donor; volunteer |
| Ben Cho, ben@example.com | · | donor,board |
After
| contact | tags | |
|---|---|---|
| Ana Ruiz | ana@example.com | donor | volunteer |
| Ben Cho | ben@example.com | donor | board |
How it happens
- The form or sheet had one box where it needed several.
- A list was flattened into text when it was exported from a system that stores lists properly.
- People added a second value to an existing cell instead of adding a row or a column.
What it breaks
- You cannot filter, count, or join on a value that is buried inside a longer string.
- The delimiter also appears inside real values, so splitting on it breaks some rows.
- Different rows use different delimiters, so any single splitting rule misses some.
How to spot it
- Search the column for commas, semicolons, slashes, pipes, and line breaks.
- Check whether values vary widely in length. Packed cells are much longer than their neighbors.
- Look for two kinds of thing in one column, such as a name and an email address.
How to fix it
- Decide the shape you need: separate columns for different facts, or one consistent delimiter for a true list.
- Split different facts into their own columns.
- For real lists, standardize on one delimiter that does not occur inside the values.
- Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
- Name variantsThe same person or organization appears under several spellings, orders, and abbreviations.