1. Computing

Remove Extra Spaces Between Data in Excel

By

Excel TRIM Function
Remove Extra Spaces in Excel

Remove Extra Spaces in Excel

© Ted French

Related Tutorial: When the TRIM Function Doesn't Work

Remove Extra Spaces with the TRIM Function

When text data is imported or copied into an Excel spreadsheet extra spaces can sometimes be included along with the text data.

The TRIM function can be used to remove the extra spaces from between words or other strings in Excel.

The TRIM Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the TRIM function is:

= TRIM ( Text )

Text - the data you want to remove spaces from. Although the argument can contain the actual data enclosed in quotation marks, it can also be a cell reference to the location of the text data in the worksheet.

In this tutorial we will remove extra spaces from data by using the data's cell reference as the function's argument.

Example: Remove Extra Spaces with the TRIM Function

As can be seen in the image above, this example will remove extra spaces from between words entered into an Excel worksheet.

  1. Enter the following text into cell D1 (be sure to include extra spaces between the words):
    Remove      Extra    Spaces     in     Excel 
    
  2. Click on cell D3 in the spreadsheet - this is where the function will be located.

  3. Click on the Formulas tab of the ribbon menu.

  4. Choose Text from the ribbon to open the function drop down list.

  5. Click on TRIM in the list to bring up the function's dialog box.

  6. In the dialog box, click on the Text line.

  7. Click on cell D1 in the spreadsheet.

  8. Click OK.

  9. The line of text Remove Extra Spaces in Excel should appear in cell D3 but with only one space between each word.

  10. When you click on cell D3 the complete function = TRIM ( D1 ) appears in the formula bar above the worksheet.

If the TRIM Function Doesn't Work

On a computer, a space between words is not a blank area but a character, and, believe it or not, there is more than one type of space character.

The TRIM function will not remove all space characters. In particular one commonly used space character that TRIM will not remove is the non-breaking space used in web pages (&nbsp).

If you have web page data with extra spaces, try this tutorial on a TRIM function alternative that may fix the problem.

©2014 About.com. All rights reserved.