How to remove blank spaces in Excel - leading, trailing, non-breaking

This tutorial shows you how to eliminate unwanted spaces in Excel using formulas and the handy Text Toolkit. Learn to remove leading and trailing spaces, extra spaces between words, non-breaking spaces, and other non-printing characters.

The biggest challenge with spaces? They're often invisible! Leading spaces before text or extra spaces between words might be noticed, but trailing spaces at the end of cells are practically undetectable. This isn't just an aesthetic issue; these extra spaces wreak havoc on formulas. Cells with seemingly identical text but differing only by a single space are treated as distinct values, leading to formula errors.

Let's tackle this! Several methods exist for removing spaces; this tutorial helps you choose the best approach based on your data and task.

Removing Spaces: Leading, Trailing, and Extra Spaces Between Words

For superfluous spaces (leading, trailing, and multiple spaces between words, but leaving single spaces intact), Excel's TRIM function is your friend:

=TRIM(A2)

(Where A2 is the cell containing the text.)

The screenshot below shows TRIM effectively removing spaces before, after, and consecutive spaces within a string.

To apply these changes, use Paste Special > Values to replace the original column with the trimmed values. For details, see How to copy values in Excel. You can also use TRIM to remove only leading spaces (see How to trim leading spaces in Excel).

Deleting Line Breaks and Non-Printing Characters

Data imports often bring unwanted non-printing characters (carriage returns, line feeds, tabs, etc.). While TRIM handles spaces, it doesn't remove these. TRIM only targets ASCII value 32 (the space character).

Combine TRIM with CLEAN to remove spaces and the first 32 non-printing ASCII characters (0-31), including line breaks (value 10):

=TRIM(CLEAN(A2))

If this joins multiple lines without spaces, use "Replace All" (Ctrl J in "Find what," space in "Replace with") or this formula to replace Carriage Return (13) and Line Feed (10) with spaces: =SUBSTITUTE(SUBSTITUTE(A2, CHAR(13)," "), CHAR(10), " "). See How to remove carriage returns (line breaks) in Excel for more.

Removing Non-Breaking Spaces

Stubborn spaces after using TRIM and CLEAN are likely non-breaking spaces (HTML character  ). Replace them with regular spaces, then use TRIM:

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

This breaks down as: CHAR(160) defines the non-breaking space; SUBSTITUTE replaces them; TRIM removes the converted spaces. For non-printing characters as well, use: =TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," ")))).

Deleting Specific Non-Printing Characters

If TRIM, CLEAN, and SUBSTITUTE fail, the characters have ASCII values outside 0-32 or 160. Use CODE to identify the value, then SUBSTITUTE to replace it with a space, and TRIM to remove it.

1. In B2, find the character's value using CODE: =CODE(LEFT(A2,1)) (leading), =CODE(RIGHT(A2,1)) (trailing), or =CODE(MID(A2,n,1)) (middle, where n is the character's position).
2. In C2, replace and trim: =TRIM(SUBSTITUTE(A2, CHAR(127), " ")) (replace CHAR(127) with the actual code).

For multiple characters, nest SUBSTITUTE functions.

Removing All Spaces

To remove all spaces, including those between words, use SUBSTITUTE to replace CHAR(32) (or simply " ") with nothing (""):

=SUBSTITUTE(A2, CHAR(32), "") or =SUBSTITUTE(A2," ","")

Counting Spaces

To count spaces: =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) (total spaces) or =LEN(A2)-LEN(TRIM(A2)) (extra spaces).

Formula-Free Space Removal

For a faster, easier way, consider using a tool like the "Trim Spaces" function in Ultimate Suite for Excel. This tool removes spaces and non-printing characters with a single click.

This concludes the tutorial. Check out Ultimate Suite for Excel for more features!

The above is the detailed content of How to remove blank spaces in Excel - leading, trailing, non-breaking. For more information, please follow other related articles on the PHP Chinese website!