cleaning data

Whitespace and case

Trim the spaces around values, squeeze the ones between words, remove them all, and put text in upper, lower or title case.

Stray spaces and inconsistent capitals are the most common reason two values that look the same don’t match. These operations fix them, in the cursor’s column or in what you select (see How data operations work).

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
6,margaret  hamilton,Boston,margaret@example.com
7,carol shaw,London,carol@example.org
8, linus torvalds,Helsinki,linus@example.fi
9,barbara liskov,Los Angeles ,liskov@example.edu
10,dennis  ritchie ,Bronxville,dmr@example.com
11,frances allen,Peru,fran@example.com
12,  edsger dijkstra,Rotterdam,ewd@example.nl
13,radia perlman ,Portsmouth,radia@example.com
14,ken thompson,New Orleans,ken@example.com
15,annie   easley,Birmingham,annie@example.com
16,shafi goldwasser,New York ,shafi@example.edu
17,donald knuth,Milwaukee,knuth@example.edu
18,sophie wilson  ,Leeds,sophie@example.co.uk
19, ivan sutherland,Hastings,ivan@example.com
20,jean sammet,New York,jean@example.com
name · Row 1

Trim Whitespace

Cells › Whitespace › Trim removes the spaces at the start and at the end of each value: ·London·· becomes London. Tabs, line breaks, non-breaking spaces and the other Unicode spaces count as spaces too. The editor marks them with dots and rings, so you can see where there are any: see Missing values and stray spaces.

Collapse Whitespace

Cells › Whitespace › Collapse trims each value and turns every run of spaces inside it into a single ordinary space: alan···turing becomes alan turing. Line breaks and tabs inside a value become spaces as well, so a value split over several lines becomes one line.

Remove All Whitespace

Cells › Whitespace › Remove All removes every space in each value, at the edges and inside: for codes, phone numbers, IBANs and amounts written with spaces between thousands (1 200 000 becomes 1200000).

Change Case

Cells › Change Case has three:

  • Cells › Change Case › UPPERCASE: madrid and Madrid become MADRID.
  • Cells › Change Case › lowercase: for email addresses, codes and tags: Ana@Example.org becomes ana@example.org.
  • Cells › Change Case › Title Case: the first letter of each word in capitals and the rest in lower case: KATHERINE johnson becomes Katherine Johnson, and o’brien becomes O’brien. Names like McDonald come out as Mcdonald, so look over a column of surnames afterwards.

To change the capitals of the column names instead, select the header row (a click on its line number) and run the operation: with the selection all in the header, only the header changes. To turn them into snake_case or camelCase, see Column names.

For accents, punctuation, curly quotes and invisible characters, see Letters, digits and other characters. To find the values that differ only in these things and make them one, see Grouping similar values.