Missing values and stray spaces — Trusty CSV User Guide
Missing values
NA, N/A, #N/A, null, NaN, none, nil, \N, -, --, ? and ., in capitals or not, are drawn in grey italics instead of their column’s colour, as databases show a NULL. That is how you tell them from real values at a glance, in any column, and they are also why a column of numbers with an NA in it still lines up as numbers. Names in the header row are never greyed.
Cells › Fill and Clear › Empty Missing Values (NA, null, -…) empties them all, so that the file has one way of saying “nothing”: see Empty and repeated values.
Spaces at the edges
A dot marks every space at the start or end of a value: a London with a space after it trips up a search, a join with another file or a duplicate check. Spaces between words aren’t marked, and inside a value with line breaks, only the spaces at the very start and end of the whole value are.
contacts.csv
9
1
2
3
4
5
6
7
id,name,city,email
1,ada lovelace,London,ada@example.org
2,grace hopper,New York,grace@example.com
3,alan turing,Manchester,alan.turing@example.org
4,hedy lamarr,Vienna,hedy@example.at
5,katherine johnson,Hampton,kj@example.com
id,name,city,email
5 rows × 4 columns
PlayA dot marks each space at the start or end of a value.
Spaces that aren’t spaces
A small ring marks every non-breaking space, and every other space that isn’t the ordinary one (thin spaces, en and em spaces and the like), wherever it is in a value. They come in with text copied from web pages, PDFs and word processors, and they look like a space but don’t compare like one. Rest the pointer on the ring to see its name and code.
Cells › Whitespace › Trim removes them at the edges of a value, Cells › Whitespace › Collapse also turns them into ordinary spaces between words, and Cells › Whitespace › Remove All removes them all: see Whitespace and case.
Characters that take no room at all, such as zero-width spaces, can’t be marked: Select Values with Unusual Characters finds them, together with these spaces.
The marks are only drawn: like the alignment, they are not in the file.