How to Remove Unwanted Spaces in Excel Cells

Sponsored Links

You must be wondering how to remove the extra spaces in excel cells, then you are in the right place, through this article, we are providing the 4 best quick and easy ways to remove the unwanted spaces in excel cells. Basically, these extra spaces arises whenever we paste the data from other sources to the excel spreadsheet. After pasting the data, the un uniformity leads to the extra or unwanted spaces along with the wanted data. Besides we have to face the trailing spaces, blanks, separators between the words and numbers which ultimately makes it difficult to use the data.

Best Ways to Remove Extra Spaces in Excel:

  • By using Trim Formula
  • By using Find & Replace
  • By using Power Query

Removing the unwanted spaces with TRIM function:

Let us try to understand it by taking an example

Example:

Step1: Add a column and name it as Trim

Step2: In the column C2, enter the formula “= TRIM (Column Number)” , let us take the column number A2, so we need to type =TRIM(A2)

Step3: Now copy the same formula by dragging the cursor or copy the formula by entering the same formula in each column where you want to remove extra spaces.

Step4: You can see the perfect data in the new column, now select the data present in the first column.

Step5: Replace the selected original data with the data copied from the new column.

Step6: Finally, remove the new column so that you can use the perfect data.

Removing the unwanted spaces with Find & Replace:

This is the second best way to remove the unwanted spaces, by using the find and replace function we can eliminate the unwanted spaces between the data from all the columns at once.

Example:

Step1:  Select a cell or several columns where you want to remove the extra spaces present between the data.

Step2: After selecting the column or columns then press Ctrl + H.

Step3: Upon pressing Ctrl + H, Find and Replace dialog box will be displayed.

Step4: for example if the extra spaces between them are 2 then hit the space bar 2 times in find what or if the space is 3 then hit the space bar 3 times in find and hit the space bar once in the replace with.

Step5: Now click on the Replace All option, and click on Ok button.

Step6: Repeat the same thing till you get a message we could not find anything to replace.

How to remove the unwanted spaces between numbers?

We can remove the unwanted spaces between the numbers by using the find and replace option and by using the formula.

Removing the unwanted spaces Between Numbers with Find & Replace:

This is one of the quick and the easiest way to remove the unwanted spaces, by using the find and replace function we can eliminate the unwanted spaces between the numbers of all the columns at once.

Example:

Step1:  Select a cell or several columns where you want to remove the extra spaces present between the numbers.

Step2: After selecting the column or columns then press Ctrl + H.

Step3: Upon pressing Ctrl + H, Find and Replace dialog box will be displayed.

Step4: for example, if the extra spaces between the numbers is 2 then hit the space bar 2 times in find what or if the space is 3 then hit the space bar 3 times in find and nothing in the replace with.

Step5: Now click on the Replace All option, and click on Ok button.

Step6: Repeat the same thing till you get a message we could not find anything to replace.

Leave Comment

Your email address will not be published. Required fields are marked *