Remove Space in Excel

Remove Spaces in Excel

While importing or copy-pasting the data from an external source, extra spaces are also copied in Excel. This makes the data disorganized and difficult to be used. The purpose of removing unwanted spaces from the excel data is to make it more presentable and readable for the user.

For example, if a cell contains “  rose,” the leading spaces may be visible to the reader. However, if “rose  ” is written in a cell, the two trailing spaces may not be easily caught by the user.

Since extra spaces may render the Excel formulas incorrect, it is essential to eliminate them.

Top 5 Methods to Remove Spaces in Excel

The five methods to remove extra spaces in Excel are listed as follows:

  1. TRIM function
  2. “Delimited” option of text to columns wizard
  3. “Fixed width” option of text to columns wizard
  4. Find and replace option
  5. SUBSTITUTE function

The user can select any of the techniques depending on the requirement. Let us discuss all the methods one by one, along with examples.

You can download this Remove Spaces Excel Template here – Remove Spaces Excel Template

#1 – TRIM Function

The TRIM function removes all spaces from a text string except for the single space between the words.

The following data contains extra spaces in column A. We want to rewrite the data without these spaces in column B with the help of the TRIM function.

Removing space Example 1

Step 1: In cell B1, enter the TRIM function.

Removing space Example 1-1

Step 2: Select the cell A1.

Step 3: Press the “Enter” key. The extra spaces from the name are removed in cell B1, as shown in the following image.

Step 4: Copy or drag the formula to the remaining cells, as shown in the following image.

#2 – “Delimited” Option of Text to Columns Wizard

The text to columnsText To ColumnsText to columns in excel is used to separate text in different columns based on some delimited or fixed width. This is done either by using a delimiter such as a comma, space or hyphen, or using fixed defined width to separate a text in the adjacent columns.read more wizard helps remove spaces in excel by splitting the data strings into separate columns. It works on cells containing numerical and textual values.

The following data contains spaces in column A. We want to split the words of column A into separate cells in such a way that the space between the strings is eliminated.

Use the “delimited” option of the text to columns wizard.

Removing space Example 2

Step 1: Select column A. In the Data tab, click “text to columns” from the “data tools” group. The same is shown in the following image.

Removing space Example 2-1

Step 2: The “convert text to columns wizard” appears, as shown in the following image. Select the option “delimited” in “choose the file type that best describes your data.” Click “next.”

Removing space Example 2-2

Step 3: Select “space” as the delimiter and click “finish.”

Removing space Example 2-3

Step 4: The text strings of column A are split into two columns. The second half of the strings is placed in column B. The spaces between the strings have also been removed.

Removing space Example 2-4

#3 – “Fixed Width” Option of Text to Columns Wizard

The “fixed width” option of the text to columns wizard helps remove extra spaces from numerical and textual data in Excel. For this, all the strings before the space should contain the same number of characters.

The following data contains random text and numbers with spaces in column A. We want to remove the spaces and split the strings into two separate columns.

Use the “fixed width” option of the text to columns wizard.

Removing space Example 3

Step 1: Select column A. In the Data tab, click “text to columns” from the “data tools” group. The same is shown in the following image.

Removing space Example 3-1

Step 2: The “convert text to columns wizard” appears, as shown in the following image. Select the option “fixed width” in “choose the file type that best describes your data.” Click “next.”

Removing space Example 3-2

Step 3: Place the cursor at the position of the space. Click “finish.”

Removing space Example 3-3

Step 4: The strings of column A are split into two columns. The second half of the strings is placed in column B. The spaces between the strings have also been removed.

Removing space Example 3-4

Note: The text to columns wizard (methods #2 and #3) splits the data into different columns.

#4 – Find and Replace Option

The find and replace option helps remove spaces from numerical and textual data in Excel.

Working on the excel data under the heading “TRIM function” (method #1), we want to remove spaces with the find and replace option.

Find & Replace - Example 4

Step 1: Press “Ctrl+H” and the “find and replace” dialog box is displayed, as shown in the following image.

Find & Replace - Example 4-1

Step 2: Enter a space in the “find what” box. Leave the “replace with” box blank. Click “replace all.”

Find & Replace - Example 4-2

Step 3: Excel displays a message stating the number of replacements. Click “Ok.”

Find & Replace - Example 4-3

Step 4: All the spaces in column A have been removed, as shown in the following image.

Find & Replace - Example 4-4

#5 – SUBSTITUTE Function

The SUBSTITUTE function helps remove the spaces by replacing them with the existing data string.

Working on the data under the heading “TRIM function” (method #1), we want to remove spaces in excel with the SUBSTITUTE function.

Substitute Function- Example 5

Step 1: In cell B1, enter the SUBSTITUTE function. Click “insert function” in the Formulas tab. The “function arguments” dialog box appears, as shown in the following image.

Substitute Function - Example 5-1

Step 2: Since the text in which characters are to be substituted is present in cell A1, enter the same in the “text” box.

Substitute Function - Example 5-2

Step 3: Since extra spaces are to be removed, enter “ ” in the “old_text” box. The blank between a pair of double quotation marks refers to a space.

Substitute Function - Example 5-3

Step 4: Since the space character is to be replaced with the existing text, enter “” in the “new_text” box. The pair of double quotation marks with no character in-between implies no spaces. Click “Ok.”

Substitute Function - Example 5-4

Step 5: The extra spaces from the name are removed in cell B1, as shown in the following image.

Substitute Function - Example 5-5

Step 6: Copy or drag the formula to the remaining cells, as shown in the following image. 

Substitute Function - Example 5-6

Note: The find and replace option and the SUBSTITUTE function (methods #4 and #5) display the entire text string together without spaces.

Frequently Asked Questions

What does it mean to remove spaces in excel and why is it important?

While working with Excel, there might be extra spaces that are not easily visible to the user. It is important to eliminate them for the following reasons:

• The spaces at inappropriate places can make the data look untidy and disordered.
• The extra spaces have a negative impact on the quality of data presentation.
• The unwanted spaces can mess up with the formulas of Excel.
• Two cells with and without spaces are considered different entries even though the data in both may be identical.

Since it is difficult to remove the extra spaces manually, there are various techniques available in Excel for such deletions.

How to remove the leading and trailing spaces in Excel with the TRIM function?

Leading spaces are the extra space characters entered at the beginning of a data string. In contrast, the trailing spaces are found at the end of a string without any character following them.

The TRIM function helps remove all the unwanted spaces of the database. The exception to this rule is the single space character inserted between the words.

The syntax of the TRIM function is stated as follows:

“TRIM(text)”

The “text” represents the cell from which excess spaces are to be removed.

For example, the text string “ Chicago, United States ” in cell C2 is changed to “Chicago, United States” by the formula “=TRIM(C2).”

How to remove all the spaces in Excel?

To remove all the spaces in Excel, use the following formula:

“=SUBSTITUTE(C2,“ ”,“”)”

The space (“ ”) is replaced by the existing data (“”) in the cell C2.

For example, the text string “Chicago, United States” in cell C2 is changed to “Chicago,United States” by the given formula.

Note: Alternatively, the find and replace option can be used to remove all the spaces of a data string.

Recommended Articles

This has been a guide to removing spaces in excel cells. We discuss how to remove Leading, Trailing, Blank, Extra Spaces along with step by step examples. You may download an Excel template from the website. For more on Excel, take a look at the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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