How to Delete Spaces in Microsoft Excel

Excel is a data analysis tool that is quite strong, and despite the fact that it has been around for more than 30 years, it is still the go-to option for every individual. It is a good idea to become proficient in some Excel functions because doing so can assist you in being more productive. These also come in quite handy when you want to get rid of little flaws like deleting excess spaces and all of that kind of stuff. They are extremely useful. The steps of removing spaces in Excel are as follows:

Read Also: How to Add Alt Text in Microsoft Excel

How to Delete Spaces in Microsoft Excel

Excel provides users with three different options for removing superfluous spaces from their work. You have the option to employ the Find and Replace feature, as well as the Trim formula and the Trim spaces tool. Let’s have a look at the different ways we might accomplish this goal.

Using Find & Replace Option

However, you can only use this method to remove spaces between words in Excel, despite the fact that it is the simplest technique to remove spaces in Excel. If there are spaces at the beginning and end of the string, we will shorten them to one but we will not completely erase them.

• Choose one or more columns from which you would want to remove excess spaces and then click the “Delete” button.
• Find and Replace can be accessed by pressing CTRL+H on your keyboard.
• In the box labelled Find what, make two presses of the space bar, and in the box labelled Replace with, make one press of the space bar.
• To save your changes, make sure to click the Replace All button and then press the OK button.

• It is necessary to repeat step 4 till the message appears. “We were unable to locate anything that could serve as a replacement.”

Using the Trim Formula

This is the correct Excel formula to use if you need to get rid of spaces in a cell or cell range. Even though it takes a little bit of time, it is an effective method that works particularly well for trailing and leading areas.

• At get started, append the Helper column to the very end of your data and give it the name “Trim.”
• Enter the Excel formula to eliminate spaces, which is denoted by =TRIM, into the first cell of the Helper column (A2).

• Make sure to paste the formula into each of the other cells as needed.
• At this point, the column that was originally there should be replaced with the one that has clean data. You can copy all of the cells in the Helper column to the clipboard by selecting all of the cells in that column and pressing the CTRL and C keys simultaneously.
• When you are finished, select the first cell in the Helper column, press the Shift+F10 combination, and then click CTRL+V to paste the same.
• If you got rid of the Helper column, you’d get rid of any unnecessary spaces that were there.

Using the Trim Spaces Tool

Excel’s Trim Spaces add-in is the most effective tool for removing unnecessary spaces from a cell’s contents. This is due to the fact that it removes any unnecessary spaces, such as leading and trailing spaces, superfluous blanks between words, non-breaking spaces, line breaks, and non-printing symbols, and it performs all of this with just three clicks.

• To get started, a trial version of Ultimate Suite for Excel should be downloaded and installed.
• You can pick the range in your table, or you can hit the CTRL plus A key combination to select all of the cells.
• The next step is to navigate to the Ablebits Data tab, which can be found just adjacent to the Data tab. Simply select the Trim Spaces symbol with your mouse.

• When you open your worksheet, the add-pane in’s will appear on the left-hand side of the page. Simply click the Trim button after selecting the appropriate checkboxes, and you will have a table that is totally clean.

FAQs

What is the function to remove extra spaces between words in column A?

In the cell that is adjacent to cell A1, type the formula =TRIM(A1). For an example, see the screenshot. Step 2. After selecting cell B1 and pressing the Enter key, move the fill handle so that it covers the entire range of cells to which you wish to apply this formula. In addition to this, any unnecessary spaces in between the characters have been taken out of the range.

Why is trim not removing spaces in Excel?

Spaces That Don’t Break Up the Flow And Are Consistent

The ASCII code for a space that does not break a line is 160. The standard space is represented by the ASCII symbol 32. Only characters with an ASCII code of 32 can have their spaces removed using the TRIM() method.

What is clean function in Excel?

It strips the text of any characters that can’t be printed. When working with text imported from other apps that contains characters that your operating system might not be able to print, use the CLEAN command. For instance, you can use CLEAN to delete some low-level computer code that is typically located at the beginning and end of data files and cannot be printed. This code can be found in many cases.

What is trim in Excel?

This function removes all of the spaces from the text, with the exception of the single spaces that are between words. If the text you have received from another application has potentially erratic spacing, you should use the TRIM command on it. It is important to note that the TRIM function was developed specifically to remove the space character with a value of 32 from ASCII text.