3 simple methods to eliminate additional spaces between words or remove all spaces from Excel cells. You can utilize the Trim function, the Excel Find & Replace feature, or a specialized Excel add-in to tidy up your cell contents.

When you copy information from an external source into an Excel sheet (such as plain text reports or figures from web pages), there’s a good chance you’ll encounter unwanted spaces alongside your valuable data. These might include initial and final spaces, multiple gaps between words, and thousands separators for numerical values.

As a result, your dataset appears disorganized and challenging to work with. It could be tricky to locate a client in the Name section because you’re searching for "John Doe," which lacks extra spaces between the names, whereas the format in your table is "John Doe." Similarly, calculations involving numbers may fail due to these unnecessary spaces.

In this guide, you’ll learn techniques to organize your data effectively.

Reduce Extra Spaces Between Words to One, Eliminate Leading/Trailing Spaces

For instance, imagine having a table with two columns. The first column, labeled “Name,” contains the name “John Doe” entered correctly without extra spaces. Other cells in this column contain excessive spaces between the first and last names, along with irrelevant spaces at the beginning and end—known as leading and trailing spaces. The second column, titled “Length,” displays the count of characters in each name.

Utilize the Trim Function to Delete Unnecessary Spaces

Excel provides the Trim function to clear out extra spaces from text. Here’s how you can apply it:

1. Add a supplementary column next to your data. Name it “Trim.”
2. In the first cell of the helper column (C2), input the formula =TRIM(A2).
   
3. Drag the formula down to cover the remaining cells in the column. Refer to Tips for Entering the Same Formula Across Multiple Cells.
4. Overwrite the original column with the cleaned-up data. Highlight all cells in the helper column, press Ctrl C to copy them, and select the first cell in the original column. Right-click and choose “Paste Values.”
   
   
5. Delete the helper column.

And there you have it! Using the Trim function, we’ve successfully removed all redundant spaces. However, this method requires more effort, particularly for large spreadsheets.

Using Find & Replace to Eliminate Extra Spaces Between Words

This approach involves fewer steps but only works for removing surplus spaces between words. It won’t entirely remove leading or trailing spaces.

1. Highlight the columns containing the data where you wish to eliminate spaces.
2. Press Ctrl H to open the "Find and Replace" dialog box.
3. Press the Spacebar twice in the Find What field and once in Replace With.
4. Click the "Replace All" button, followed by pressing "OK" to dismiss the confirmation dialog.
   
5. Repeat Step 4 until you receive the message “We couldn’t find anything to replace.”

Three Clicks for Neat Data with Trim Spaces Tool

If you frequently import data into Excel from external sources and spend considerable time refining your tables, the Trim Spaces tool simplifies tasks like cleaning data imported from the web or other external locations. It handles leading and trailing spaces, excess spaces between words, non-breaking spaces, and line breaks.

To remove all extra spaces in your worksheet, including those between words, follow these steps:

1. Download and install a trial version of Ultimate Suite for Excel.
2. Select the range within your table where you want to remove unwanted spaces. For new tables, I typically press Ctrl A to process all columns simultaneously.
3. Navigate to the Ablebits Data tab and click on the Trim Spaces icon.
   
4. A pane will appear on the left side of your worksheet. Choose the desired options, click the Trim button, and admire your freshly organized table.
   

Isn’t this quicker than the earlier methods? For those who handle data processing regularly, this tool saves countless hours.

Remove All Spaces Between Numbers

Imagine you have a workbook filled with numbers where digits (like thousands, millions, or billions) are separated by spaces. This causes Excel to interpret these entries as text, preventing mathematical operations.

The simplest way to eliminate these spaces is via the standard Excel Find & Replace feature:

• Press Ctrl Space to highlight all cells in a column.
• Press Ctrl H to launch the "Find & Replace" dialog box.
• Press the Spacebar in the Find What field and ensure the "Replace with" field remains blank.
• Click the "Replace All" button, then press "OK." Presto! All spaces are gone.
  

Using a Formula to Remove All Spaces

If you need to delete every single space—perhaps within a formula chain—you can set up a helper column and enter the following formula: =SUBSTITUTE(A1," ","").

Here, A1 refers to the first cell of the column holding numbers or words where all spaces should be eliminated.

Proceed with the steps outlined under the previous formula-based removal method for extra spaces between words.

Video: How to Remove Spaces in Excel

The above is the detailed content of 3 ways to remove spaces between words / numbers in Excel cells. For more information, please follow other related articles on the PHP Chinese website!