## 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**

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!