Remove Unwanted Space Characters in Excel!

Remove Unwanted Space Characters in Excel:

In this article, we will learn the easy ways to Remove Unwanted Space Characters in the data in Excel 365. While working in Excel spreadsheets, some unwanted space characters are included. These unwanted space characters should be removed from the data so that only we can get valid data. Here are some examples of Unwanted Space Characters in the data table in Excel.

How to Remove Unwanted Spaces using TRIM Function?

• The TRIM Function removes all the extra spaces from the data in Excel except a single space between two words.
• For example, you need to enter ” =TRIM(B2) ” or “ TRIM(“Keep Learning!!”) ” to remove the extra spaces.
• Refer to the below example image.

• You can use Function Arguments Dialog Box to remove extra spaces.
• In that dialog box, you have to enter the cell address and Click the Ok button.
• It will give the result without Extra space.

How to Remove Unwanted Spaces between words Using Find and Replace?

• This method is applicable only for ** Removing Unwanted Spaces Between Words** in Excel with the help of the Find and Replace Option.
• To do this, you need to ** select the range of cells** you want to remove unwanted spaces.
• On the Home tab, in the Editing section, Select Find and Select Option and click Replace Option from the list.
• Now, it will display the Find and Replace Dialog Box like below.

• You have to choose the Options button to view the more advanced ** Find and Replace** options in that dialog box.
• In Find what option, you need to give two space characters by pressing the Space bar twice on the keyboard.
• Replace with an option, and you need to add a single space character by pressing the Space bar once on the keyboard.
• Check Within option selected sheet , ** Look in** option selected Formulas.
• Now, press Replace All button to Remove the Unwanted Space Characters.

How to Remove Unwanted Spaces with Power Query?

Here we will follow some set of instructions to ** Remove Unwanted Spaces Using** Power Query in Excel.

• On the Data tab, you have to select the From Table or Range command.

• After clicking that icon , it will display the Create Table Dialog box. You need to select the range of cells and press the Ok button.

• Now, it will display the Power Query Editor Dialog Box. Then you need to Right-click on the column heading. It will display the menu list.
• From that menu, you have to select the Transform option, then Select Trim.

• It doesn’t remove the unwanted spaces from that table, so you need to take more steps to remove extra spaces.
• To do this, Right-Click on the column name and Select Replace values from the menu list.

• In ** the Value To Find** Option, you have to provide two Space Characters as input.
• In ** Replace With** Option, you have to provide Single Space Character as input.
• Press the Ok button. You have to repeat these steps until extra spaces are removed from the data.
• To get the cleaned data back to Excel, you have to close and load the data.
• On the Home tab in the Power Query editor, Click the Close and Load option. Then select the table ** and Select the **Location to load the table in Excel.

• It will give the result like the above image.

A Brief Summary:

In the above article, you can clearly understand the ways to Remove Unwanted Space Characters in the data in MS Excel 365. Drop your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!