How do I remove all spaces from a string in Excel?

To remove all spaces from a string in Excel, you can use either the SUBSTITUTE function for a formula-based solution or the Find and Replace feature for a quick, in-place modification.

Completely Removing All Spaces with the SUBSTITUTE Function

The SUBSTITUTE function in Excel is ideal for removing all instances of a specific character or string. To remove all spaces, you simply replace a single space character (" ") with an empty string ("").

How to Use the SUBSTITUTE Function

1. Select an empty cell where you want the result to appear (e.g., B1 if your original string is in A1).
2. Enter the formula:

   =SUBSTITUTE(A1, " ", "")

   • A1: This is the cell containing the string from which you want to remove spaces.
   • " ": This is the old_text (a single space) that you want to replace.
   • "": This is the new_text (an empty string) that replaces all spaces.
3. Press Enter. The cell will now display the string without any spaces.
4. Drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to other cells in your column.

Example:

Original String (A1)	Formula (B1)	Result (B1)
This is a test	=SUBSTITUTE(A1, " ", "")	Thisisatest
Data Entry	=SUBSTITUTE(A2, " ", "")	DataEntry
NoSpacesHere	=SUBSTITUTE(A3, " ", "")	NoSpacesHere

For more details, refer to the official SUBSTITUTE function (Microsoft Support) documentation.

Removing All Spaces Using Excel's Find and Replace Feature

For a quick, direct modification of your data without using formulas, Excel's Find and Replace feature is highly effective. This method is destructive, meaning it overwrites your original data.

Steps for Find and Replace

1. Select the range or column that contains the strings from which you want to remove spaces. If you want to apply it to the entire sheet, click on any cell and proceed.
2. Press CTRL+F (or Cmd+F on Mac) to open the Find dialog box.
3. Click on the Replace tab.
4. In the Find what: field, type a single space (" "). Ensure there is only one space character in this field.
5. Leave the Replace with: field completely empty. Do not type anything in it.
6. Click Replace All. Excel will notify you how many replacements were made.

This method will instantly remove all spaces (including leading, trailing, and internal spaces) from the selected cells.

Cleaning Up Excess Spaces (Not All Spaces) with the TRIM Function

While the question asks for removing all spaces, it's important to differentiate this from removing excess spaces. The TRIM function is designed to clean up common spacing issues by:

• Removing all leading and trailing spaces.
• Reducing multiple spaces between words to a single space.

How to Use the TRIM Function

1. Select an empty cell where you want the cleaned string (e.g., B1 if your original string is in A1).
2. Enter the formula:

   =TRIM(A1)

   • A1: This is the cell containing the string you want to clean.
3. Press Enter.
4. Drag the fill handle down to apply the formula to other cells.

Example:

Original String (A1)	Formula (B1)	Result (B1)
Hello World	=TRIM(A1)	Hello World
Another Example	=TRIM(A2)	Another Example

Notice that TRIM leaves a single space between words, unlike SUBSTITUTE (with "") or Find & Replace, which remove all spaces.

For more details, refer to the official TRIM function (Microsoft Support) documentation.

Choosing the Right Method for Your Needs

Here's a quick comparison to help you decide which method is best for your specific task:

Feature	SUBSTITUTE (Remove All Spaces)	Find & Replace (Remove All Spaces)	TRIM (Remove Excess Spaces)
Purpose	Completely removes all spaces	Completely removes all spaces	Standardizes spacing
Output	No spaces	No spaces	Single internal space
Destructive?	No (result in new cell)	Yes (modifies original data)	No (result in new cell)
Best For	Dynamic, non-destructive operations; integration into larger formulas	Quick, in-place bulk changes on static data	General data cleaning and formatting
Flexibility	Can specify which instance of space to replace	Replaces all instances uniformly	Focuses only on space reduction