How to Remove Extra Spaces in Excel (5 Easy Ways)

Certainly, Microsoft Excel has a wide range of professional applications. In fact, we use Excel to manage data, process it, and get valuable information from that data. At times, we need to process raw data from a source file, and this may introduce errors like extra spaces in the data. Keeping this in mind, this article describes 5 ways how to remove extra spaces in Excel with suitable examples and explanations.

The animated GIF shown below is an overview of this article, which represents the removal of extra spaces.

Overview of removing extra spaces in Excel

In the following sections, we’ll learn more about the dataset and observe each method in detail.

How to Remove Extra Spaces in Excel: 5 Simple Ways

First and foremost, let’s assume the List of Student Information dataset shown in the B4:C14 cells containing the “ID” and “Details” columns respectively. Here, we want to get rid of the extra spaces present in the “Details” column using Excel functions and features. So, let’s glance at each method with the necessary illustrations.

Dataset for remove extra spaces in excel

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

1. Removing Extra Spaces with Excel Functions

Now, functions are the heart and soul of a spreadsheet, so let’s see how to remove extra spaces in Excel by utilizing functions.

1.1 Using TRIM Function to Remove All Extra Spaces

In the first place, we’ll apply the TRIM function that removes additional spaces within a text.

📌 Steps:

  • Initially, go to the D5 cell >> enter the formula given below >> press ENTER.


Here, the C5 cell refers to the “Details” text regarding the “ID A-001”.

remove extra spaces in excel with TRIM function

1.2 Combining TRIM, MID, FIND & LEN Functions to Remove Only Leading Spaces

Besides, we may combine the TRIM, MID, FIND, and LEN functions to remove the leading spaces in a string of text. Here, the combination of these functions helps identify the starting position of the text characters and erase the leading space.

📌 Steps:

  • First of all, move to the D5 cell >> enter the formula given below >> click ENTER.


Formula Breakdown
  • LEN(C5) → returns the number of characters in a string of text. Here, the C5 cell is the text argument that yields the value 32.
    • Output → 32
  • MID(TRIM(C5),1,1) returns the characters from the middle of a text string, given the starting position and length. Here, the C5 cell is the text argument, 1 is the start_num argument, and 1 is the num_chars argument such that the function returns the first character from the left side.
    •  Output → “J”
  • FIND(MID(TRIM(C5),1,1),C5) becomes
    • FIND(“J”,C5) returns the starting position of one text string within another text string. Here, “J” is the find_text argument while C5 is the within_text argument. Here, the FIND function returns the position of the numeric values in the string of text.
    • Output → 1
  • MID(C5,FIND(MID(TRIM(C5),1,1),C5),LEN(C5)) becomes
    • MID(C5,1,32) Here, the C5 cell is the text argument, 1 is the start_num argument, and 32 is the num_chars argument such that the function returns the first character from the left side.
    • Output → “Jose,        Age:10,     Grade:4”

remove extra spaces in excel with TRIM, MID, FIND & LEN functions

1.3 Implementing CLEAN, SUBSTITUTE, and CHAR Functions to Eliminate Line Breaks

In addition, we can also fuse the TRIM, CLEAN, SUBSTITUTE, and CHAR functions to eliminate line breaks.

📌 Steps:

  • In the first place, enter the D5 cell >> type the following expression >> hit ENTER.


Formula Breakdown
  • CHAR(160) → returns the character specified by the code number from the character set from your computer. Here, the 160 cell is the number argument that represents the white space character.
    • Output → ” “
  • SUBSTITUTE(C5,CHAR(160),””) → replaces existing text with new text in a text string. Here, the C5 refers to the text argument while the CHAR(160) represents the old_text argument, and the “” points to the new_text argument. Here, the function substitutes white spaces with blanks.
    • Output → “Jose, Age:    10, Grade:    4  “
  • CLEAN(SUBSTITUTE(C5,CHAR(160),””)) becomes
    • CLEAN(“Jose, Age: 10, Grade: 4”) removes all nonprintable characters from text. Here, “Jose, Age: 10, Grade: 4” is the text argument.
    • Output → “Jose, Age:    10, Grade:    4  “
  • TRIM(CLEAN(SUBSTITUTE(C5,CHAR(160),””))) becomes
    • TRIM(“Jose, Age: 10, Grade: 4”) → here, the “Jose, Age: 10, Grade: 4” is the text argument and the function gets rid of excess spaces after the text.
    • Output → “Jose, Age: 10, Grade: 4”

remove extra spaces in excel with TRIM, CLEAN, SUBSTITUTE, and CHAR 

2. Removing Spaces Before Numbers Employing Find & Replace Feature

For one thing, if combining complex formulas and expressions does not suit you, then Excel’s Find and Replace command can help solve your problems.

📌 Steps:

  • At the very beginning, select the D5:D14 cells >> navigate to the Find & Select drop-down >> choose the Replace option.

Applying Find & Replace command 

  • Next, in the Find what field, type a blank space >> leave the Replace with field empty >> press the Replace All button.

entering find what and replace with and pressing replace all

Ta-dah! That is how simple it is to remove spaces in Excel.

remove extra spaces in excel with Find and Replace command

3. Utilizing Flash Fill to Remove Extra Spaces

Moreover, another smart feature of Excel is the Flash Fill option which recognizes a pattern and auto-fills the rest of the column without any formulas like magic! 🙃

📌 Steps:

  • To begin with, type the desired output in the D5 cell >> choose the D5:D14 cells >> jump to the Data tab >> click Flash Fill.

using flash fill to generate output

Boom! The end result appears in the image below.

remove extra spaces in excel with flash fill

4. Erasing Spaces with Power Query Tool

Conversely, the Power Query tool, an often overlooked feature of Excel, is quickly becoming my favorite tool for analyzing large sets of data. Since it is jam-packed with useful and handy features, let’s now see it in action.

📌 Steps:

  • To start with, move the cursor to the B4 cell >> press CTRL + T >> click on OK to insert an Excel Table.

Inserting excel table with keyboard shortcut

  • Not long after, proceed to the Data tab >>  choose From Table/Range option.

Exporting table to power query editor

  • At this point, this opens the Power Query editor, follow the steps shown in the GIF for a live demonstration.

Trim spaces in the Power Query editor

  • Afterward, press the Close & Load drop-down >> choose the Close & Load To option.

Closing and loading as table in excel

  • Furthermore, in the Import Data window, press OK.

Importing data to Excel as Excel Table

Voila! The final result should look like the screenshot shown below.

remove extra spaces in excel using Power Query tool

5. Applying VBA Code for Removing Extra Spaces

Last but not least, If you often need to remove extra spaces, then you may consider the VBA Code below. It’s simple & easy, just follow along.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Launching visual basic editor from developer tab

  • Second, go to the Insert tab >> select Module.

Inserting module

For ease of reference, copy the code from here and paste it into the window as shown below.

Sub remove_spaces()
Dim cell As Range
For Each cell In Selection.Cells
cell.Value = Trim(cell.Value)
Next cell
End Sub

VBA code for removing extra spaces in excel

Code Breakdown

In the following section, we’ll explain the VBA code used to erase extra spaces.

  • In the first portion, the sub-routine is given a name, here it is remove_spaces().
  • Next, define the variable cell as Range.
  • Lastly, use a For Loop to iterate through each value and apply the Trim function to get rid of the spaces.

VBA Code explanation

  • Third, copy and paste the “Details” column into the “Output” column >> click the Macros button >> press Run.

Executing macro

Consequently, the results should look like the figure given below.

remove extra spaces in excel with VBA code

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section

Download Practice Workbook


In essence, this article shows 5 effective methods on how to remove extra spaces in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

Related Articles

<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF