How to remove spaces between words in Microsoft Excel

In Microsoft Excel, removing spaces between words refers to eliminating any extra spaces or gaps that may exist within a cell’s content. This action is often performed to consolidate text or improve data consistency. Removing spaces between words can be useful when dealing with imported or copied data that may contain unnecessary spaces.

In this tutorial, we will learn how to remove spaces between words in Microsoft Excel. Eliminating spaces between words is an important task in Microsoft Excel and can be accomplished by utilizing the TRIM function, SUBSTITUTE function, or the Flash Fill feature when applicable patterns exist among the target words.

Method 1: Utilizing the TRIM Function

The TRIM function in Excel is used to remove extra spaces from a text string. It removes all leading and trailing spaces from the specified text, as well as any extra spaces between words within the text. The syntax for the TRIM function is as follows:

TRIM(text)

Here, 

text: 

This is the text string or reference to a cell containing the text that you want to trim. The TRIM function does not alter the original text string; instead, it returns a new text string with the extra spaces removed.

Step 1 – Choose the Targeted Cell

• Choose the targeted cell where the spaces are to be removed from the words.

Step 2 – Enter the Trim Function

• Énter the TRIM function in the cell.

            TRIM(A2:A7)

• We have entered the complete range containing the words with spaces so that we can apply the function as an Array Formula. 

Step 3 – Press the CTRL+SHIFT+ENTER Keys

• Press the CTRL+SHIFT+ENTER keys to apply the function as an Array formula i.e. the function will be applied to the complete range instantly.

Method 2: Utilizing the Flash Fill Feature

Flash Fill is a powerful feature in Microsoft Excel that allows you to quickly fill in data in a column based on patterns or examples. It is designed to automate data entry tasks and can be especially helpful when dealing with repetitive data patterns.

Step 1 – Remove the Spaces from the First Text Manually

• Remove the spaces from the first text manually.

Step 2 – Press the CTRL+E Keys

• Press the CTRL+E keys to utilize the Flash Fill feature.

Method 3: Using SUBSTITUTE Function

The SUBSTITUTE function in Excel is used to replace specific instances of text within a larger text string. It allows you to replace a specific occurrence or all occurrences of a specified substring with a new substring. 

The syntax for the SUBSTITUTE function is as follows:

SUBSTITUTE(text, old_text, new_text, [instance_num])

text: The text or reference to the cell containing the text within which you want to perform the replacement.

old_text: The text you want to replace.

new_text: The text you want to replace old_text with.

instance_num (optional): Specifies which occurrence of old_text you want to replace. If omitted, all occurrences of old_text will be replaced.

Step 1 – Choose the Targeted Cell

• Choose the targeted cell where the spaces are to be removed from the words.

Step 2 – Enter the SUBSTITUTE Function

• Énter the SUBSTITUTE function in the cell.

            SUBSTITUTE(A2, “”, “”)

• We have entered the complete range containing the words with spaces so that we can apply the function as an Array Formula. 
• The second parameter is a space in inverted commas i.e. the text to be removed.
• The third parameter is an empty string i.e. “”.
• Hit the Enter key.

Step 3 – Utilize Autofill to Apply the Function on Each Cell

• Utilize Autofill to apply the SUBSTITUTE function on each cell.