How to Replace Non-Breaking Spaces with Regular Spaces in Excel

Cleaning imported web data often leads to frustration when standard formulas fail due to hidden, non-breaking spaces. This issue frequently arises when exporting financial reports from standard funding sources. Achieving flawless data alignment grants analysts immediate reporting integrity. However, the key stipulation is that Excel's standard TRIM function cannot detect these CHAR(160) characters. Resolving this requires using =SUBSTITUTE(A1, CHAR(160), " ") to convert them into regular spaces. Below, we will break down this formula's structure and explore advanced nesting techniques for complete automation.

Introduction: The Invisible Data Saboteur

Have you ever imported data into Excel only to find that your VLOOKUP, MATCH, or SUMIF formulas inexplicably return errors? You double-check the spelling, scan for typos, and visually confirm that the lookup value matches the source data perfectly. Yet, Excel insists they are different.

More often than not, the culprit behind this frustrating issue is an invisible character: the non-breaking space. While it looks identical to a regular space on your screen, Excel treats it as an entirely different entity. In this comprehensive guide, we will explore why non-breaking spaces break your spreadsheets and how to write the perfect Excel formulas to identify, replace, and clean them from your data.

What is a Non-Breaking Space?

In computer text processing, characters are represented by numeric codes. A standard, regular space-the one you produce by pressing the spacebar on your keyboard-is represented by ASCII code 32.

A non-breaking space (often abbreviated as NBSP) is represented by ASCII code 160 (or Unicode U+00A0). In web development (HTML), it is written as  . Its primary purpose is to prevent an automatic line break at its position. For example, web designers use it to keep monetary symbols and numbers (like "$ 100") or dates (like "January 1") on the same line.

When you copy data from web browsers, emails, PDFs, or external ERP systems (like SAP, Salesforce, or Oracle) and paste it into Excel, these non-breaking spaces tag along. Because Excel distinguishes characters by their ASCII codes, "New York" with a regular space (ASCII 32) is not equal to "New York" with a non-breaking space (ASCII 160).

The Core Formula: Replacing Non-Breaking Spaces with Regular Spaces

To fix this issue using a formula, we must combine two built-in Excel functions: SUBSTITUTE and CHAR.

The SUBSTITUTE function replaces existing text with new text in a string. The CHAR function returns the character associated with a specific ASCII code number.

The Formula

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

How It Works

1. CHAR(160): This evaluates to the non-breaking space character.
2. " ": This represents a standard, regular space (ASCII 32).
3. SUBSTITUTE(A2, ...): Excel scans the text in cell A2, finds every occurrence of the non-breaking space, and swaps it with a regular space.

The Ultimate Data Cleaning Combo: TRIM + SUBSTITUTE

Simply converting non-breaking spaces to regular spaces is often only the first step. Web-scraped data frequently contains excess spaces at the beginning or end of text strings, or double spaces between words.

Ordinarily, you would use Excel's TRIM function to remove these extra spaces. However, TRIM is designed to only recognize and remove standard spaces (ASCII 32). It will completely ignore non-breaking spaces (ASCII 160).

To clean your data completely, you must first convert the non-breaking spaces to regular spaces, and then wrap that result in the TRIM function:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Why this works so well:

The inner nested function SUBSTITUTE(A2, CHAR(160), " ") executes first, transforming all invisible non-breaking spaces into regular spaces. Once they are regular spaces, the outer TRIM function can identify and strip away any leading, trailing, or duplicate spaces.

Removing Non-Breaking Spaces Entirely

In some situations, such as dealing with account numbers, phone numbers, or product SKUs, you might want to eliminate all spaces entirely rather than converting them. To strip non-breaking spaces completely from a text string, replace the regular space parameter with an empty string (""):

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

If your string contains a mixture of both regular spaces and non-breaking spaces that you want completely removed, you can nest multiple SUBSTITUTE functions:

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

How to Diagnose If You Have Non-Breaking Spaces

Because non-breaking spaces look identical to regular spaces, you need a way to prove they exist before writing cleanup formulas. You can use a diagnostic formula combining CODE and MID.

Suppose you have a text string in cell A2 that looks like "Item 1", but your formulas aren't working. To find the ASCII code of the 5th character (the space), use this formula:

=CODE(MID(A2, 5, 1))

If the formula returns 32, it is a standard space. If it returns 160, you have confirmed the presence of a non-breaking space.

Alternative Method: Using Find and Replace (No Formulas)

If you prefer not to use helper columns and formulas, you can strip non-breaking spaces using Excel's native Find and Replace dialog box. However, because you cannot easily type a non-breaking space, you must use a special keyboard shortcut.

1. Select the range of cells you want to clean.
2. Press Ctrl + H to open the Find and Replace dialog box.
3. Click into the Find what input field.
4. Hold down the Alt key on your keyboard, and using the numeric keypad (on the right side of your keyboard), type 0160. Release the Alt key. (Note: You won't see anything appear in the box, but the cursor will move slightly).
5. Click into the Replace with input field and press your spacebar once to enter a regular space.
6. Click Replace All.

Warning: This keyboard shortcut method requires a dedicated physical numeric keypad. It typically does not work using the standard number keys located above the QWERTY keys on laptops.

Automating with VBA (For Large-Scale Sheets)

If you regularly import massive files containing non-breaking spaces, manual editing and formula copy-pasting can quickly become tedious. You can automate this process across an entire selected sheet using a simple VBA macro.

To add this macro to your workbook:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module window:

Sub CleanNonBreakingSpaces()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In ActiveSheet.UsedRange
        If Not cell.HasFormula Then
            If InStr(cell.Value, Chr(160)) > 0 Then
                cell.Value = Replace(cell.Value, Chr(160), " ")
                cell.Value = Application.WorksheetFunction.Trim(cell.Value)
            End If
        End If
    Next cell
    Application.ScreenUpdating = True
    MsgBox "Data cleanup complete!", vbInformation
End Sub

This macro loops through every non-formula cell in your active sheet, replaces all non-breaking spaces with standard spaces, applies a trim to clean up trailing/leading spaces, and updates the cells instantly.

Summary of Best Practices

• Standardize Imports: Whenever you paste data from an external database or website, assume non-breaking spaces are present.
• Use Helper Columns: Apply the =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) formula to clean your data in a secondary column, then copy and paste the clean data as "Values" over your original data.
• Formulas over Manual Edits: While Find and Replace is fast, formulas are safer to use in automated, repeatable templates.