TRIM Function in Excel: Remove Extra Spaces from Text

Have you ever copied data from a website, PDF or another Excel file and noticed extra spaces between words?

These extra spaces can make your data look untidy. They can also cause problems when you use formulas such as VLOOKUP, XLOOKUP or COUNTIF.

Instead of removing spaces manually, you can use the TRIM function in Excel.

The TRIM function removes extra spaces from text and keeps only single spaces between words.

Let’s see how it works.

What Is the TRIM Function in Excel?

The TRIM function in Excel removes unwanted spaces from text.

It removes:

• Spaces at the beginning of the text.
• Spaces at the end of the text.
• Extra spaces between words.

However, it keeps a single space between words.

For example, if a cell contains `  Excel    Tutorial  `, the TRIM function returns `Excel Tutorial`.

TRIM Function Syntax:

The syntax of the TRIM function is:

=TRIM(text)

Argument:

• text: The text from which you want to remove extra spaces. It can be a text value or a cell reference.

How to Use the TRIM Function in Excel?

Let’s understand how to use the TRIM function with a practical example.

Practical Example: Clean Customer Names in Excel

Imagine you have a customer list copied from a website or another source. The list contains customer names, email IDs, and phone numbers.

Some customer names have extra spaces at the beginning, end, or between words.

These extra spaces can make it difficult to match customer names or identify duplicate records.

You can use the TRIM function to clean the customer names.

To clean the customer names, follow these steps:

1. Insert a new column after Customer Name. The new column will become column B.

2. Enter Cleaned Name as the heading in cell B1.

3. Click cell B2 and enter `=TRIM(A2)`.

4. Press Enter.

5. Drag the formula down to clean the remaining customer names.

Excel removes the extra spaces and displays the cleaned names in column B.

You can now use the cleaned names for matching, sorting, or identifying duplicate records.

How to Replace Original Text with TRIM Results:

The TRIM function does not change the original text. It returns the cleaned text in a separate cell.

For example:

• Original customer names are in column A.
• Enter `=TRIM(A2)` in cell B2.
• Excel displays the cleaned name in B2.

If you want to replace the original names with the cleaned results:

1. Select the cells containing the TRIM formulas.
2. Press Ctrl + C to copy them.
3. Select the original cells in column A.
4. Right-click and choose Paste Special.
5. Select Values and click OK.

Now the original names are replaced with the cleaned names.

Alternatively: If you don’t need the original names, you can simply delete column A and rename column B as Customer Name.

Important Things to Know About the TRIM Function:

• TRIM removes leading and trailing spaces.
• It reduces multiple spaces between words to a single space.
• It keeps a single space between words.
• It does not remove all types of invisible characters.
• The TRIM function returns cleaned text in a new cell. It does not directly change the original data.

Note: Excel’s TRIM function is designed to remove the standard space character (ASCII 32). If your data contains non-breaking spaces copied from websites, TRIM may not remove them. You can use SUBSTITUTE along with TRIM to handle these spaces.

For example:

=TRIM(SUBSTITUTE(A2,CHAR(160),” “))

This formula replaces non-breaking spaces with regular spaces before removing the extra spaces.

Frequently Asked Questions:

1. What does the TRIM function do in Excel?

The TRIM function removes extra spaces from text. It removes spaces at the beginning and end and reduces multiple spaces between words to a single space.

2. Does TRIM remove all spaces between words?

No. TRIM keeps one space between words. It removes only the extra spaces.

For example, `Excel    Tutorial` becomes `Excel Tutorial`.

3. Does the TRIM function change the original text?

No. The TRIM function returns the cleaned text in the cell containing the formula. The original text remains unchanged until you replace it with the results.

4. Why is TRIM not removing spaces in Excel?

The TRIM function removes standard spaces (ASCII 32). If your text contains non-breaking spaces or other invisible characters, TRIM may not remove them.

You can use the SUBSTITUTE function with CHAR(160) to remove non-breaking spaces.

5. Can I use TRIM with multiple cells?

Yes. Enter the TRIM formula for the first cell and drag the fill handle down to apply it to other cells.

Final Thoughts:

The TRIM function is a simple but useful Excel function for cleaning text.

Whenever you copy data from websites, PDFs, or other sources, extra spaces can create problems. Instead of removing them manually, use TRIM to clean your data quickly.

For more useful Excel functions, explore our Excel Functions guide.