Missing values and stray spaces
Values that mean “nothing here” are shown in grey, and spaces at the edges of a value, or spaces that aren’t ordinary ones, are marked.
Two things that make data hard to clean are easy to miss in a text file: values that stand for nothing, and spaces you can’t see. Trusty CSV shows both.
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.
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.
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
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.
removes them at the edges of a value, also turns them into ordinary spaces between words, and 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.