cleaning data

Finding the cells to fix

Select the empty cells of a column, its repeated values, the values of another type, the longest ones or those with unusual characters, and then fix just those.

Selection › Select Cells has commands that select cells by what they hold. Each one selects every cell it finds, quotes included, with a cursor in each, and says at the bottom of the window how many it found. Then you can go through them, type to replace them all at once, or run an operation of the Cells menu, which with a selection changes only the selected cells (see How data operations work).

To see first how many there are of each, open the Column profile, whose figures and values select their cells too.

They look in the cursor’s column, below the header. With a selection, they look only in the cells it touches; with everything selected (Ctrl+A⌘A), in the whole file.

Command Selects
Select Empty Cells the cells with nothing in them, or only spaces
Select Duplicate Values the values that appear in more than one cell of their column, every one of them
Select Values of Another Type in a column of numbers, dates, times or email addresses, the values that aren’t one
Select Values Longer Than… the values longer than a number of characters you type
Select Values with Unusual Characters the values with invisible characters, unusual spaces or �

Empty cells

Selection › Select Cells › Empty puts a cursor in every empty cell, so that you can look at them and type what goes there. To write the same text in all of them without looking, Fill Empty Cells does it in one go. Missing values such as NA or null aren’t empty: Empty Missing Values turns them into empty cells first.

Duplicate values

Selection › Select Cells › Duplicate Values selects every cell whose value is in another cell of the same column too, the first one included. In a column that should have a different value in every row, such as ids or email addresses, these are the ones to fix. Capitals and spaces count (Ana and ana are different values), and empty cells and missing values are never duplicates. To remove rows that repeat others, see Duplicate rows.

Values of another type

When most values of a column are numbers, dates, times or email addresses, the editor lines the column up by them (see Numbers, dates and other values), and a single value of another kind, say a tbd among prices, breaks the alignment. Selection › Select Cells › Values of Another Type selects those values, and the message says what the others are: Selected 3 values that aren’t numbers in “price”. If most of the column is text, there’s no type to compare with, and it says so. Empty cells and missing values don’t count.

Long values

Selection › Select Cells › Values Longer Than… asks for a number of characters, and selects the values that have more. It’s for columns that have to fit a limit, such as a database column of 50 characters or a text message of 160. An emoji or a Chinese character counts as one, and so does each line break inside a value.

Unusual characters

Selection › Select Cells › Values with Unusual Characters selects the values that have characters that are hard to see or that point to a problem:

  • invisible ones: zero-width spaces, soft hyphens, byte order marks and control characters;
  • non-breaking spaces and the other spaces that aren’t the ordinary one, which the editor marks with a ring (see Missing values and stray spaces);
  • �, the replacement character, which is what’s left of a letter when a file was read with the wrong encoding.

Remove Invisible Characters removes the first kind, and Collapse Whitespace turns the spaces into ordinary ones. A � usually means the file has to be opened again with its right encoding: see Encodings and line breaks.

How many

These commands select up to 10,000 cells. When there are more, they select the first 10,000 and the message says how many there are in all. The operations of the Cells menu have no limit: for changes to every cell of a long column, run them with just a cursor in it.