cleaning data

Letters, digits and other characters

Remove letters, digits, punctuation or accents, straighten curly quotes, decode HTML entities and get rid of invisible characters.

Cells › Characters changes the characters inside each value, in the cursor’s column or in what you select (see How data operations work). Like every operation, each one is one step of Ctrl+Z⌘Z.

Letters, digits and punctuation

Command What it removes Example
Remove Letters every letter, in any alphabet, with its accents ABC-123 becomes -123
Remove Digits every digit, and number characters such as ½ and ² Room 12B becomes Room B
Remove Punctuation and Symbols punctuation, and symbols such as $, +, %, © and emoji (555) 123-4567 becomes 555 1234567

They add up: to keep only the digits of a phone number, remove the punctuation and then all the spaces with Cells › Whitespace › Remove All. Where a removed character sat between two words, its spaces stay: Cells › Whitespace › Collapse tidies them.

Remove Punctuation and Symbols takes decimal points and minus signs too, so -12.50 becomes 1250. Use it on text, not on amounts.

Accents and curly quotes

  • Cells › Characters › Remove Accents takes the accents off letters: Ángela Núñez becomes Angela Nunez, Kraków becomes Krakow and Łódź becomes Lodz. It’s for comparing names written with and without accents, and for systems that only take plain letters. Only Latin and Greek letters change: in Russian й is a letter of its own, and in Hindi or Arabic the marks are vowels, so they stay.
  • Cells › Characters › Straighten Curly Quotes turns ‘ ’ into ' and “ ” into ". Word processors and web pages write the curly ones, and most programs that read data expect the straight ones. Guillemets (« ») and the primes of feet and inches (′ ″) stay. A value that ends up with a " in it gets the quotes the file needs around it.
  • Cells › Characters › Decode HTML Entities is for text copied or exported from web pages, with & for &, é for é or ' for '. Each entity is decoded once, so < becomes <, and names it doesn’t know are left as they are.

Invisible characters

Cells › Characters › Remove Invisible Characters removes characters that take no room on the screen but are in the text: zero-width spaces, soft hyphens, byte order marks in the middle of a value, the marks that set the direction of text, and control characters. They come in with text copied from web pages and PDFs, and they make two values that look the same compare as different. Tabs and line breaks stay (for those, see Whitespace and case), and so do the zero-width joiners that emoji and some scripts need.

Cells › Characters › Normalize Unicode (NFC) makes letters that look the same into the same text. An é can be written as one character or as an e followed by an accent: they look identical, but they don’t match in a search, a sort or a duplicate check. This writes them all the first way, Unicode’s normal form C, the one almost every program uses.

To see which values have invisible characters before changing anything, use Select Values with Unusual Characters. The Quality Check lists them too, and also the letters written with a separate accent.