How to Use Excel Formula to Remove Spaces?

Sharing is caring!

Do you have data in Microsoft Excel with unwanted spaces that you need to remove? Extra spaces in your cells can cause issues with sorting, filtering, and analyzing your data. Fortunately, Excel provides several formulas and methods to remove spaces quickly and easily. In this article, we’ll cover the most effective Excel formulas to eliminate spaces from your data.

What Are the Types of Spaces in Excel?

Before we look at how to remove spaces in Excel, it’s important to understand the different types of spaces you might encounter:

• Leading spaces: Spaces at the beginning of a cell
• Trailing spaces: Spaces at the end of a cell
• Double spaces: Two or more spaces between words or characters in a cell

Depending on the type of spaces you need to remove, you’ll use different formulas and functions.

Using the TRIM Function to Remove Spaces

The TRIM function is the most straightforward way to remove extra spaces from your data in Excel. TRIM removes all leading, trailing, and double spaces, leaving behind only single spaces between words.

How to Use TRIM Function

To use the TRIM function:

1. Select a blank cell where you want the output to appear
2. Type =TRIM(
3. Click on the cell containing the data you want to trim
4. Press Enter

For example, if you have data with extra spaces in cell A1, you would use the following formula in another cell:

=TRIM(A1)

This will return the text from A1 with all extra spaces removed.

TRIM Function Syntax

The full syntax for the TRIM function is:

=TRIM(text)

Where “text” is the cell reference or text string containing the data you want to trim.

Removing All Spaces with the SUBSTITUTE Function

While TRIM is great for removing extra spaces, it still leaves single spaces between words. If you need to remove all spaces, including single spaces, the SUBSTITUTE function is the way to go.

How SUBSTITUTE Works

SUBSTITUTE allows you to replace a specific character or string with another character or string. To remove all spaces, we tell SUBSTITUTE to replace the space character (” “) with an empty string (“”).

SUBSTITUTE Syntax and Use

The syntax for SUBSTITUTE to remove all spaces is:

=SUBSTITUTE(text,” “,””)

• “text” is the cell or text containing the spaces you want to remove
• The first ” ” is the space character you want to replace
• The second “” is an empty string to replace the spaces with

For example, to remove all spaces from cell A1, you would use:

=SUBSTITUTE(A1,” “,””)

This will eliminate every space in cell A1.

Combining TRIM and SUBSTITUTE

In some cases, you may want to remove extra spaces while still keeping single spaces between words. You can achieve this by nesting the TRIM and SUBSTITUTE functions together.

How to Combine TRIM and SUBSTITUTE

To use TRIM and SUBSTITUTE together:

1. Start with the TRIM function
2. Wrap SUBSTITUTE around it to replace any remaining double spaces with single spaces

The formula would look like this:

=TRIM(SUBSTITUTE(text,” “,” “))

This first applies SUBSTITUTE to replace double spaces with single spaces, then passes the result to TRIM to remove any leading or trailing spaces.

Example of Nested TRIM and SUBSTITUTE

Suppose you have data in A1 with a mixture of leading, trailing, and double spaces. To remove the extra spaces while keeping single spaces between words, use:

=TRIM(SUBSTITUTE(A1,” “,” “))

This will clean up the spacing in A1 to remove all unwanted spaces.

Removing Line Breaks with TRIM and SUBSTITUTE

In addition to spaces, your Excel data may contain line breaks within cells that you need to remove. A line break is represented by the characters CHAR(10) or CHAR(13) in Excel.

Using TRIM to Remove Line Breaks

To remove line breaks with TRIM:

1. Use TRIM to remove any extra spaces
2. Wrap SUBSTITUTE around TRIM to replace CHAR(10) and CHAR(13) with an empty string

The full formula is:

=SUBSTITUTE(SUBSTITUTE(TRIM(text),CHAR(10),””),CHAR(13),””)

This removes line breaks along with any leading, trailing, or extra spaces.

Example of Removing Line Breaks

If cell A1 contains:

” Example
text with
line breaks “

You can use this formula to remove the line breaks and extra spaces:

=SUBSTITUTE(SUBSTITUTE(TRIM(A1),CHAR(10),””),CHAR(13),””)

The result will be:

“Example text with line breaks”

Removing Spaces from Multiple Cells

So far we’ve looked at removing spaces from a single cell. But what if you need to clean up spacing across an entire column or range?

Removing Spaces in a Column

To remove spaces from a whole column:

1. Select a blank cell at the top of a new column
2. Enter the TRIM, SUBSTITUTE, or combined formula for the first cell in the original column
3. Press Enter to get the result for the first cell
4. Double-click the fill handle (small square in the bottom-right of the cell) to copy the formula down the entire column

This will instantly remove the spaces from every cell in the column.

Removing Spaces from a Range

If your data is in a range of cells rather than a column, you can still remove all the spaces at once:

1. Select a blank area of the worksheet with the same dimensions as your original range
2. In the top-left cell of the blank area, enter the spacing removal formula for the top-left cell of the original range
3. Press Ctrl+Enter to populate the formula across the entire blank range
4. Copy the new range and paste the values over the original data

Following these steps will remove the unwanted spaces from a table or any other range in your worksheet.

Tips for Preventing Unwanted Spaces

While the formulas covered here make removing spaces easy, it’s better to avoid extra spacing in the first place. Some tips:

• Be consistent with your spacing when manually entering data
• Use data validation to restrict cell entries and prevent extra spaces
• Import data carefully, checking for spacing issues and fixing them before loading into Excel
• Clean your data with spacing formulas immediately after importing

By following these best practices, you can minimize the need to remove spaces later on.

Final Thoughts

Removing unwanted spaces from your Excel data is essential for keeping your worksheets clean, consistent, and easy to work with. The TRIM function eliminates leading, trailing, and extra spaces, while SUBSTITUTE can remove all spaces or specific characters. By combining these functions and applying them across columns and ranges, you can quickly remove extra spaces from any dataset.

Aim to prevent spacing issues when possible, but don’t hesitate to use these formulas when you need them. With the tools and techniques covered here, you have everything you need to keep your Excel data free of unwanted spaces.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.