Excel formula to remove space between words in a cell

Learn excel formula to remove space between words in a cell with practical examples, diagrams, and best practices. Covers excel-formula, excel-2010 development techniques with visual explanations.
Tags:
Categories:

Excel Formula to Remove Spaces Between Words in a Cell

Excel spreadsheet showing a cell with text and spaces highlighted, with a formula bar above.

Learn how to effectively remove unwanted spaces between words within a single Excel cell using various formulas, enhancing data cleanliness and consistency.

Cleaning data in Excel often involves removing extra spaces. While Excel's TRIM function is excellent for removing leading, trailing, and excessive spaces between words (reducing multiple spaces to a single space), it doesn't remove all spaces between words. This article will guide you through formulas to completely eliminate all spaces between words in a cell, leaving a continuous string of characters.

Understanding the Challenge

The built-in TRIM function is a common first step for data cleaning. It handles most spacing issues by removing all spaces from the beginning and end of text, and reducing any multiple spaces between words to a single space. However, if your goal is to concatenate words without any spaces at all, TRIM alone is insufficient. You need a more robust approach to target and remove every instance of a space character within the text string.

flowchart TD
    A["Input Text (e.g., '  Hello   World  ')"]
    B{"Apply TRIM()"}
    C["TRIM() Result (e.g., 'Hello World')"]
    D{"Goal: Remove ALL Spaces"}
    E["Desired Output (e.g., 'HelloWorld')"]
    A --> B
    B --> C
    C --> D
    D --> E

Illustrating the difference between TRIM() and removing all spaces.

Method 1: Using SUBSTITUTE for Complete Space Removal

The most straightforward and widely applicable method to remove all spaces between words in an Excel cell is to use the SUBSTITUTE function. This function allows you to replace all occurrences of a specific text string with another text string. By replacing a space character (" ") with an empty string (""), you effectively remove all spaces.

=SUBSTITUTE(A1," ","")

Formula to remove all spaces from cell A1.

In this formula:

  • A1 refers to the cell containing the text from which you want to remove spaces.
  • " " (a space enclosed in double quotes) is the text you want to find.
  • "" (two double quotes with nothing in between) is the text you want to replace the spaces with (an empty string).

Method 2: Handling Non-Breaking Spaces (NBSP)

Sometimes, spaces might not be regular space characters but non-breaking spaces (NBSP), often represented by CHAR(160). These can occur when data is copied from web pages or other applications. The SUBSTITUTE function, by default, only targets regular spaces (CHAR(32)). To remove non-breaking spaces, you'll need to specifically target CHAR(160).

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ","")

Formula to remove both regular spaces and non-breaking spaces from cell A1.

This formula nests two SUBSTITUTE functions:

  1. The inner SUBSTITUTE(A1,CHAR(160),"") first replaces all non-breaking spaces with an empty string.
  2. The outer SUBSTITUTE(...," ","") then takes the result of the inner function and replaces all regular spaces with an empty string.