How to remove spaces in Excel

Which one depends on whether the spaces between words should stay.

TRIM is the one to use on names, company names and emails, where a stray space at the end is the problem and the spaces between words are meant to be there. It removes the spaces before the text and after it, and leaves one space between words. The example cleans names in column A.

Find and Replace removes every space, including the ones between words, so use it on values that should have none: phone numbers, IDs, ZIP codes, or emails that were typed with a space in them. It changes the cells in place.

Phone numbers need care. A cell left with only digits turns into a number, so 0131 496 0509 becomes 1314960509 and the leading 0 is gone. For phone numbers, use SUBSTITUTE, below, which keeps the result as text.

SUBSTITUTE replaces one piece of text with another inside a cell. With an empty replacement it removes it: =SUBSTITUTE(A2, " ", "") returns A2 with every space taken out, and leaves A2 itself as it is.

This is the formula for spaces between numbers. A phone number typed as 415 555 0123 becomes 4155550123. Keep the result as text: converting a phone number to a number drops a leading 0 and a + sign. For a figure like 1 250 000 that should be a number, wrap it in VALUE: =VALUE(SUBSTITUTE(A2, " ", "")) returns 1250000.

To remove only the first space, or any other single one, SUBSTITUTE takes a fourth argument, the occurrence: =SUBSTITUTE(A2, " ", "", 1) removes the first space and keeps the rest.

TRIM only removes the ordinary space character. Text copied from a web page, a PDF or some exports often carries non-breaking spaces instead. They look the same on screen, but they are a different character, number 160, and TRIM leaves them alone. Find and Replace with a typed space misses them too.

Turn them into ordinary spaces first, then trim: =TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")). Use UNICHAR, not CHAR: in Excel for Mac, CHAR(160) is a different character, so the formula changes nothing there. UNICHAR(160) is the non-breaking space on Windows and Mac. To remove them in place with Find and Replace on Windows, click in Find what, hold Alt and type 0160 on the number keypad, type one ordinary space in Replace with, then Replace All. Replacing them with nothing joins the words on either side, so Westmarch Ltd becomes WestmarchLtd. Then use TRIM, as above, to clear the spaces left at the start and end.

Line breaks inside a cell are a different character again, CHAR(10). CLEAN removes them along with other characters that don't print, but it leaves nothing in their place, so Brightlane and Inc on two lines become BrightlaneInc. Swap line breaks for a space first: =TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, UNICHAR(160), " "), CHAR(10), " "))) covers all three. CLEAN doesn't remove non-breaking spaces on its own, which is why the first SUBSTITUTE stays in the formula.

TRIM and SUBSTITUTE work the same way in Google Sheets: =TRIM(A2) for extra spaces, =SUBSTITUTE(A2, " ", "") for all of them.

If the data came in through File > Import with Convert text to numbers, dates, and formulas left on, the default, Sheets has already removed the spaces at the start and end of each cell. Double spaces between words and non-breaking spaces stay.

Sheets also has a menu command that trims in place, with no helper column. Select the cells and go to Data > Data cleanup > Trim whitespace. It removes leading, trailing and repeated spaces. It leaves non-breaking spaces, so for text copied from the web, use =TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")) as in Excel. On a UK account the menu reads Data clean-up.

To remove every space in place, use Edit > Find and replace (Ctrl+H, or Cmd+Shift+H on a Mac): a space in Find, nothing in Replace with, then Replace all. As in Excel, a phone number left with only digits turns into a number and loses its leading 0.

A space you can't see makes two values different. jane@westmarch.com and the same address with a space at the end are two values to Excel, so Remove Duplicates keeps both rows, and a VLOOKUP on that email returns #N/A. Clean the spaces first, then remove duplicates or run the lookup.

The same goes for a CRM import. Some importers trim spaces and some don't, so clean them in the file and the result doesn't depend on which one you use.

Operelio's Find & Replace works on a CSV or Excel file without opening it. Add a rule, turn on Advanced patterns, and use ^\s+|\s+$ as the find with Replace left empty: it removes spaces at the start and end of every cell, non-breaking spaces included. \s+ removes every space. Set Runs on to the columns you mean, check the preview, and run it. Your original file is never changed.

If the file is going into HubSpot, Salesforce or Pipedrive, the CRM Formatter also trims emails and strips spaces from phone numbers on the way in.