How To Remove Spaces in Excel

Excel cells might include lots of empty spaces if you copy and paste data from websites, or other documents, into your spreadsheets. Of course, you could manually edit each of the cells to delete the blank spacing as required. However, that might take a while if there are lots of cells that include expansive empty spacing. It’s better to remove spacing in cells with some of Excel’s functions and tools. This is how you can remove spaces in Excel spreadsheets with TRIM, SUBSTITUTE, the Find and Replace tool and Kutools add-on.

The TRIM Function

TRIM is an Excel function that removes most spacing from a text string except single spacing. So this is a good function to remove all leading and trailing spaces in cells with. However, it doesn’t work with non-breaking space characters ( ).

As an example, open a blank Excel spreadsheet and enter ‘  5634   525626  ‘ in cell B2 with two leading spaces, three spaces between the numbers and two trailing spaces at the end. Then click cell C2 and press the fx button to open the Insert Function window. Select TRIM and press OK to open its window below.

Press the Text field’s cell reference button and select B2. Press the OK button to close the window. That will remove all the leading and trailing spaces and two of the spaces between the numbers.

To remove spacing in multiple column cells, you can copy the TRIM function to adjacent cells. First, select C2 and press the Ctrl + C hotkey. Then you should left-click the bottom right corner of C2, and drag the rectangle over the other cells to copy the function to them as below.

The SUBSTITUTE Function

SUBSTITUTE is a better function if you need to delete all spacing from cells. This function replaces text or numerical values with alternative data, but you can also remove all empty spacing with it. The SUBSTITUTE syntax is: SUBSTITUTE(text, old_text, new_text, [instance_num]).

To add SUBSTITUTE to your spreadsheet, select cell D2 and press the fx button. Select SUBSTITUTE from the Insert Function window. Press OK to open the window directly below.

Press the Text field’s cell reference button. Select the cell you entered 5634 525626 in, which was B2. Then input “ ” in the Old_text box and input “” in New_text box as shown in the shot below.

Now press the OK button to add the function to spreadsheet. That will delete all the spacing from the text in cell B2 as below. You can copy the function to other cells much the same as TRIM.

The Find and Replace Tool

Find and Replace is a tool that’s similar to the SUBSTITUTE function. It finds and replaces text in cells. You can also delete leading, trailing and excess spacing between text within a range of selected cells with the tool. The advantage Find and Replace tool is that you can remove the spaces directly from the cells without adding extra spreadsheet columns for functions.

First, input the numbers ‘  435   789  ‘ in cell B3 with two leading and two trailing spaces plus three spaces between the values. Select B3 and press the Ctrl + H hotkey to open the Find and Replace window shown directly below. Usually, you would enter text to replace in the Find what box and then input the replacement in Replace with. To remove spacing from the selected cell, press Space twice in the Find what text box.

Press the Replace All button. A dialog window pops up informing you that Excel has made some replacements. Press OK to close that window. Now B3 will include just the one space between the two numbers with no leading or trailing spacing.

To erase all cell spacing with the Find and Replace tool, select B3 again and press Ctrl + H. Now enter the one space in the Find what text box. The Replace with field should not including any spacing. That will remove the one remaining space in B3 when you press Replace All.

Remove Spaces with Kutools

Kutools is one of Excel’s best add-ons, which is retailing at $39 on this website. Kutools also includes a Remove Spaces tool to delete cell spacing with. If you’ve installed that add-on, select a range of cells to remove spacing from. Then click the Kutools tab in Excel, Text and select Remove Spaces from the menu. That opens a Remove Spaces window from which you can select All spaces to delete the spacing. Alternatively, you can also select options to erase leading, trailing or excess spacing.

So Excel has a few functions, tools and add-ons to remove spacing from spreadsheet cells with. They’ll certainly come in handy for removing space formatting from pasted cell content. For further details on how to remove extra spaces in cells, check out this YouTube video.