How to Use the TRIM Function to Delete Spaces in Excel

Sometimes, when you import or copy data from other sources into Excel, you may encounter extra spaces in the text. These spaces can cause problems when you try to sort, filter, or manipulate the data. For example, if you have a list of names with extra spaces, Excel may not recognize them as the same values and treat them differently.

To remove these unwanted spaces, you can use the TRIM function in Excel. The TRIM function is a text function that removes all leading and trailing spaces from a text string, and also reduces any multiple spaces within the text to a single space.

In this article, we will explain how to use the TRIM function in Excel, and show you a detailed example with real data. We will also discuss some other approaches to delete spaces in Excel.

The syntax of the TRIM function is:

=TRIM(text)

where text is the text string that you want to remove spaces from. You can enter the text directly, or refer to a cell that contains the text.

For example, if you have the text " Hello World " in cell A1, you can use the formula =TRIM(A1) in another cell to get the result "Hello World".

The TRIM function only removes spaces that are ASCII character 32. It does not remove other types of whitespace characters, such as tabs, line breaks, or non-breaking spaces. To remove these characters, you may need to use other functions, such as CLEAN, SUBSTITUTE, or REPLACE.

Example

Suppose you have a list of names in column A that have extra spaces before, after, or between the names. You want to remove these spaces and have a clean list of names in column B. Here is how you can use the TRIM function to achieve this:

1. In cell B1, enter the formula =TRIM(A1). This will remove all the extra spaces from the name in cell A1 and return the result in cell B1.
2. Copy the formula in cell B1 and paste it to the rest of the cells in column B. Alternatively, you can drag the fill handle in the bottom right corner of cell B1 to fill down the formula to the other cells in column B.
3. You will see that the names in column B are now free of any extra spaces. You can also compare the length of the names in column A and column B by using the LEN function. For example, in cell C1, enter the formula =LEN(A1) to get the length of the name in cell A1, and in cell D1, enter the formula =LEN(B1) to get the length of the name in cell B1. You will see that the length of the name in cell B1 is less than or equal to the length of the name in cell A1, depending on how many spaces were removed.

Here is a sample table that shows the result of using the TRIM function on a list of names:

Other Approaches to Delete Spaces in Excel

Besides using the TRIM function, there are some other ways to delete spaces in Excel. Here are some of them:

• Using the Find and Replace feature: You can use the Find and Replace feature in Excel to find all the spaces in your data and replace them with nothing. To do this, select the range of cells that you want to remove spaces from, press Ctrl+H to open the Find and Replace dialog box, enter a space in the Find what box, leave the Replace with box blank, and click Replace All. This will replace all the spaces in your selected range with nothing. However, this method will also remove the spaces that are needed between the words, so you may need to manually add them back later.
• Using the Text to Columns feature: You can use the Text to Columns feature in Excel to split your text data into multiple columns based on a delimiter, such as a space. To do this, select the range of cells that you want to remove spaces from, go to the Data tab, and click Text to Columns. In the wizard that opens, choose Delimited as the data type, and click Next. In the next step, check the box for Space as the delimiter, and click Next. In the final step, choose the destination for the split data, and click Finish. This will split your text data into multiple columns based on the spaces. You can then copy and paste the columns that you need, and delete the ones that you don’t.
• Using a VBA macro: You can also use a VBA macro to delete spaces in Excel. For example, you can use the following macro to remove all the leading, trailing, and multiple spaces from the text in the selected range:

Sub DeleteSpaces()
    Dim rng As Range
    Dim cell As Range
    Set rng = Selection
    For Each cell In rng
        cell.Value = Application.Trim(cell.Value)
    Next cell
End Sub

To use this macro, you need to press Alt+F11 to open the Visual Basic Editor, insert a new module, and paste the code in the module. Then, you can select the range of cells that you want to remove spaces from, and run the macro by pressing F5 or clicking the Run button.