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 text 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
For help with this example, see the image above.- Enter the following text into cell D1 (be sure to include extra spaces between the words):
Remove Extra Spaces in Excel
- Click on cell D3 in the spreadsheet - this is where the function will be located.
- Click on the Formulas tab of the ribbon menu.
- Choose Text from the ribbon to open the function drop down list.
- Click on TRIM in the list to bring up the function's dialog box.
- In the dialog box, click on the Text line.
- Click on cell D1 in the spreadsheet.
- Click OK.
- The line of text Remove Extra Spaces in Excel should appear in cell D3 but with only one space between each word.
- When you click on cell D3 the complete function = TRIM ( D1 ) appears in the formula bar above the worksheet.
When 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 ( ).
If you have imported or copied data from web pages you may not be able to remove the extra spaces with the TRIM function if they are created by non-breaking spaces.
If this is the case, there is a tutorial on Microsoft's website that gives a detailed, if somewhat technical, explanation on how to remove non-breaking space characters from imported web page data.
Related Tutorials
