How To Remove Extra Spaces in Excel?

How To Remove Extra Spaces in Excel?

A “space” between words is count as invisible character in computer. Here we will learn how to remove extra spaces in excel

The binary code of Space is 0010000. However, Space is also count as a character in Microsoft Excel.

There are types of spaces in excel cell which you apply intentionally (Single space between two words) or Accidentally (Multiple Spaces in Cell).

Usually there are two types of spaces in excel Leading Space and Trailing Space.

Leading Space is given before any word start in excel.

And Trailing space is given after any word ends (usually between words).

What is the impact of Multiple spaces in Excel?

Before learning how to remove extra spaces in excel we need to learn what is the impact of it.

If there is any Leading Space (Before the word starts) in any cell then formula works incorrectly or It doesn’t work at all.

For example when you want to count the number of characters in excel you use LEN Function to count.

LEN Function will count the number of characters in excel including the space as a character.

So,

The Result of LEN function will be the count of  Number of Words + Spaces in Cell.

As I mentioned before that Space is also a character in excel.

In the same way if you apply any other Text function or Math function then It may give you #NA error which means the value you are looking for does not exist in the database.

The #NA error is quite frustrating and very common in Excel.

More Details   SUMIF Function in Excel: The Complete Guide for 2019

Because We barely think the we would have mistakenly added leading space in any of the cell.

We need to clean our data first to avoid similar errors in excel.

We will be using Two methods for cleaning the data removing unwanted/extra or multiple spaces in excel.

The first and most commonly used method is to use function. It is widely accepted and faster then later one.

We will be using TRIM Function to trim down the extra spaces in excel.

Here is the step by step instructions for you to follow.

Type The =TRIM function and select the data cell which you want to trim down the extra spaces.

Hit the Enter key to compile the formula.

See. How easy it is.

Using Trim function will work fast and remove spaces but it will create another column of your data (Helper Column).

However, you can replace the incorrect column with the new TRIM function column,

But

before you do that Copy and Paste the Helper column as value otherwise It will reflect #NA error if you delete or overlap (copy) the data in existing columns.

The second method is not use any function and use our old Find and Replace Option (Ctrl +H).

Using the Find and Replace option is even very easy.

We will be Finding Space and replacing it with null (NO SPACE). Its easier then you are reading it to be.

Here are the Step by Step Instructions.

Use CTRL +H Shortcut to pop up find and replace tool.

More Details   Why Nobody is Using This Beautiful LOOKUP Function?

In the “Find” box give a space.

In the “Replace” box leave it as it is. No need to type anything.

Hit Replace all.

It will replace all the Space character with none (that is no space between, before and after any word).

Which is Better?

It depends on the data type and situation.

If you have sensitive data in any column then you can add helper column.

Adding a helper column with TRIM Function is the best option. It is widely accepted due to its precision.

It precise because it will remove spaces wherever there are more than one space.

As you all know spaces can be multiple.

It will remove all the multiple spaces and keep one space between words.

If you know want to apply for whole sheet of database at once where you are sure that there will be multiple spaces then use find and replace tool and in the Find box type Two spaces then replace all with None (Null space).

Akash Gurjar

Hi, I am Akash. I am Hobbyist Blogger and YouTube Creator. I like to explore and learn new things all the time. I own a website easyexplain.info and a YouTube Channel EasyExplain. Join Me.

Leave a Reply