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.
See in the column Name Using TRIM Function, there are no unnecessary spaces in the names.
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.
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.
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.
See in the Name column, that there are no unnecessary spaces.
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.
➤ 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.
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
- How to Remove White Space in Excel
- How to Remove Space after Number in Excel
- How to Remove Leading Spaces in Excel
- How to Remove the Trailing Spaces in Excel
- How to Remove Blank Spaces in Excel
- How to Remove Space Before Text in Excel
- How to Remove Space in Excel after Text
- How to Remove Space between Rows in Excel
- How to Remove Tab Space from Excel
<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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!