reading your data

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.

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
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
5 rows × 4 columns
A 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.