How to Find and Replace Space in Excel: 5 Methods

Method 1- Inserting TRIM Function to Find and Replace Space in Excel

➤ Type the following formula in cell F4.

=TRIM(C4)

➤ Press ENTER.

See in cell F4 there is space only between first name and last name.

➤ Drag down the formula with the Fill Handle tool.

How to Find and Replace Space in Excel

See in the column Name Using TRIM Function, there are no unnecessary spaces in the names.

How to Find and Replace Space in Excel


Method 2 – Using SUBSTITUTE Function to Find and Replace Space

➤ Begin with, we will write the following formula in cell F4.

=SUBSTITUTE(D4," ","")

We replaced the spaces from the D4 cell with empty values.

➤ Press ENTER.

We can see in cell F4 that there are no spaces between the numbers.

➤ Drag down the formula with the Fill Handle tool.

See in the Salary Using SUBSTITUTE Function column that there are no spaces between the numbers of salary.

How to Find and Replace Space in Excel

 


Method 3 – Finding and Replacing Space with Find and Replace Tool

➤Select the entire dataset of the Salary column.

➤ Go to Home tab in the ribbon, and we have to select the Editing option.

➤ Select the Find and Select option.

➤ Select the Replace option.

Find and Replace window will appear.

➤ Our salary column number has a space between them; we will give a space in the Find What box.

➤ Give no space in the Replace with box, and we will click on Replace All.

A confirmation window will appear.

➤ Click OK.

See that there is no space between the numbers in the Salary column.

How to Find and Replace Space in Excel


Method 4 – Removing Extra Spaces with Excel Power Query Tool

➤ Select the entire dataset of the Name column from C3 to C13.

➤ Go to the Data tab in the ribbon.

➤ Select From Table/Range option.

Create Table window will appear.

➤ Mark the box My table has headers.

➤ Click OK.

A Power Query Editor window will appear.

➤ Right-click on the Name column.

➤ Select Transform and then select Trim.

➤ Go to the Home tab in the Power Query window.

➤Select Close & Load, and then select Close & Load to option.

➤ Select Table4(2) to load our data.

How to Find and Replace Space in Excel

See in the Name column, that there are no unnecessary spaces.

How to Find and Replace Space in Excel

Using this method you can only replace the space from the first and last of a string.


Method 5 – Applying VBA Macro to Find and Replace Space

➤Type ALT+F11 in our active sheet.

How to Find and Replace Space in Excel

➤ A VBA Application window will appear.

➤ Double click on our sheet6, and a VBA editor window will appear.

➤ Type the following code in our VBA editor window.

Sub ReplaceSpaces()
Dim SpcRng As Range
Dim SpcCells As Range
Dim TempCells As String
Set SpcRng = Selection
For Each SpcCells In SpcRng
TempCells = SpcCells.Value
TempCells = Trim(TempCells)
SpcCells.Value = TempCells
Next SpcCells
End Sub

Close the editor window, and go to the sheet (we will go to our Sheet6).

➤ Select the data range of the Name column and type ALT+F8.

➤ A Macro window will appear, and we will click Run.

See that there is no space before the first name and after the last name.

How to Find and Replace Space in Excel

Our applied VBA TRIM function only has the functionality to remove spaces from the first and last of a string, so the VBA code only finds and replaces the spaces from the first and last of the string.


Download Workbook


Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

2 Comments
  1. I’m using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    and these methods do not work.
    Excel refuses to find (or replace) space characters.
    TRIM and SUBSTITUTE functions do not remove excess spaces either.

  2. Hello Mark W
    I hope after reading my reply, you will be able to solve the problem.

    1. First, let me explain to you how the TRIM function works.
    Suppose, you have the name “ Joe Louis “, and you can see this name has leading, middle, and trailing spaces. In that case, the TRIM function will work.
    The result will be like “Joe Louis”.

    However, if the name is like “ Joe Lou is “, the TRIM function will only remove the leading and trailing space of the name. It will not remove the space between letters.
    The final result of the above name will be “Joe Lou is”.

    Now, suppose you have a number like “ 12 24 5 6 “, in this case, the TRIM function will only remove the leading and trailing spaces. Therefore, after applying the TRIM function.
    The number will look like “12 23 5 6”.

    I hope that now you will easily understand in which situations, the TRIM function works.

    2. After that, let me explain to you how the SUBSTITUTE function works.

    If you have a number like “ 1 2 45 7 “ then the SUBSTITUTE function will eliminate all the spaces. Here, suppose the old number is in cell C4. Therefore, you have to type =SUBSTITUTE(C4,” “,””).
    After that, the number will become like “12457”.

    Next, if you have a word where the letters have spaces between them like if you have “ Yell ow “ in a cell, the SUBSTITUTE function will remove the spaces. Here, suppose the old number is in cell C4. Therefore, you have to type =SUBSTITUTE(C4,” “,””).
    The result will be “Yellow”.

    However, if a Text has spaces between words then we have to identify and add the space in the SUBSTITUTE function properly. Let’s say, the name “ Adam Smith “ is present in cell C4. You can easily notice that there are three spaces between Adam and Smith. Along with that, there are lading and trailing spaces. we have to type =SUBSTITUTE(F16,” “,” “), here you to give three spaces in between the first double quote. Along with that, make sure to keep one space between the second double quote, otherwise both the words will merge into one word.
    The outcome will be “Adam Smith”

    I hope that when using the SUBSTITUTE function if you can identify the spaces between words, and add the space properly in the formula, your problem will be solved.

    3. Let us now discuss how Find and Replace works.
    Find and Replace is a useful feature to replace spaces between numbers in a cell. If you want to replace spaces between numbers, Find and Replace is an effective and easy way.

    However, For different numbers of spaces between words, we have to identify those spaces, and in the Find what box we need to press those Exact numbers of spaces. Otherwise, the Find and Replace will not work. Therefore, if different numbers of spaces between text are present in different cells then the cells need a unique number of spaces in the Find what box.

    I hope you can identify the spaces between words, and in the Find what box you can press exactly the same number of spaces. Hence, your problem might be solved.

    4. When different cell content has a different number of spaces, Power Query is extensively useful to remove those spaces.
    As in your comment, you did not mention anything regarding Power Query, I highly suggest you use Power Query. I am hopeful that it will solve your problem.

    Thank you for your comment. I hope you will now be able to solve your problem. If, however, these methods still do not work for you, please share your Excel file in the comment section. This will help me to understand the problem, and I will try my best to solve the problem.
    Regards!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo