How to Find and Replace Space in Excel (5 Methods)

Written by Afia Kona

Last updated: Dec 19, 2023

There are several methods to Find and Replace Space in Excel. In this article, we will demonstrate to you the 5 most effective and handy methods to do the task step by step.

The following Employee List table shows ID No, Name and Salary of the employees. Here, we can see there is space in the names and in the salaries. We have to Find and Replace Space in Excel using different methods. Here, we will use Excel 365. You can use any available Excel version.

1. Inserting TRIM Function to Find and Replace Space in Excel

Here, we want to remove unnecessary spaces in the Name column, and we want space only between first name and last name. The TRIM function will be most efficient in that case.

➤ First of all, we will type the following formula in cell F4.

=TRIM(C4)

➤ Then, press ENTER.

Now, we can see in cell F4 there is space only between first name and last name.

➤ We can drag down the formula with the Fill Handle tool.

Finally, we can see in the column Name Using TRIM Function, that there are no unnecessary spaces in the names.

2. Using SUBSTITUTE Function to Find and Replace Space

Here, we want to remove spaces between the numbers in the Salary column. The SUBSTITUTE function will be helpful in this case.

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

=SUBSTITUTE(D4," ","")

Here, we’ve replaced the spaces from the D4 cell with empty value.

➤ Then, we have to press ENTER.

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

➤ We will now drag down the formula with the Fill Handle tool.

Finally, we can see in the Salary Using SUBSTITUTE Function column, that there are no spaces between the numbers of salary.

3. Finding and Replacing Space with Find and Replace Tool

In this method, using the Find and Replace option, we want to find space in the Salary column, and we want to replace those spaces.

➤ First, we have to select the entire dataset of the Salary column.

➤ After that, we will go to the Home tab in the ribbon, and we have to select the Editing option.

➤ After that, we will select the Find and Select option.

➤ Then, select the Replace option.

After that a Find and Replace window will appear.

➤ Now, as our salary column number has a space between them, we will give a space in the Find What box.

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

Then, a confirmation window will appear.

➤ We will click OK.

Finally, we can see that there is no space between the numbers in the Salary column.

4. Removing Extra Spaces with Excel Power Query Tool

In this method, we will use Power Query to remove spaces from the Name column.

➤ First of all, we will select the entire dataset of the Name column from C3 to C13. For a practical purpose, it’s ideal to select the entire table, but we are selecting a specific column to demonstrate only.

➤ After that, we will go to the Data tab in the ribbon.

➤ We will select From Table/Range option.

Now, a Create Table window will appear.

➤ We will mark the box My table has headers.

➤ Click OK.

Now, a Power Query Editor window will appear.

➤ Right click on the Name column.

➤ We will select Transform and then select Trim.

➤ After that, we will go to the Home tab in the Power Query window.

➤ We will select Close & Load, and then select Close & Load to option.

➤ After that, we will select Table4(2) to load our data.

Finally, we can see in the Name column, that there are no unnecessary spaces.

Note that, using this method you can only replace the space from the first and last of a string.

5. Applying VBA Macro to Find and Replace Space

In this method, we will use the VBA code to find and replace space in the Name column before the first name and after the last name.

➤ First of all, we have to type ALT+F11 in our active sheet.

➤ After that, a VBA Application window will appear.

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

➤ Now, we will 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

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

➤ Now, we will select the data range of the Name column and type ALT+F8.

➤ After that, a Macro window will appear, and we will click on Run.

Finally, we can see that there is no space before the first name and after the last name.

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

Find and Replace Space.xlsm

Conclusion

Here, we tried to show you some simple, easy, and effective methods that will help you to find and replace space in Excel. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to know us in the comment section.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Mark W Jul 12, 2022 at 7:52 PM

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.
Afia Jul 21, 2022 at 1:15 PM

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!