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).
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
Trim Whitespace
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
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
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
has three:
- :
madridandMadridbecomeMADRID. - : for email addresses, codes and tags:
Ana@Example.orgbecomesana@example.org. - : the first letter of each word in capitals and the rest in lower case:
KATHERINE johnsonbecomesKatherine Johnson, ando’brienbecomesO’brien. Names likeMcDonaldcome out asMcdonald, 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.