Learn How to Remove Extra Spaces from Excel

of 01

Remove Extra Spaces with Excel's TRIM Function

Remove Extra Spaces From Text Data in Excel with the TRIM Function
Remove Extra Spaces From Text Data in Excel with the TRIM Function. © Ted French

When text data is imported or copied into an Excel worksheet 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 - as shown in cell A6 in the image above.

The function requires, however, that the original data remain present somewhere otherwise the function's output will disappear.

Usually, it is best to keep the original data - it can be hidden or located on another worksheet to keep it out of the way.

Using Paste Values With the TRIM Function

If, however, the original text is no longer needed, Excel's paste values option makes it possible to keep the edited text while removing the original data and the TRIM function.

How this works, as outlined below, is that paste values is used to paste the TRIM function output back on top of the original data or into any other desired location.

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. This argument can be:

  • the actual data enclosed in quotation marks; 
  • a cell reference to the location of the text data in the worksheet.

TRIM Function Example

In the image above, the TRIM function - located in cell A6 - is used to remove extra spaces from in front of and from between the text data located in cell A4 of the worksheet.

The function's output in A6 is then copied and pasted - using paste values - back into cell A4. Doing so places an exact copy of the content in A6 into cell A4 - but without the TRIM function.

The last step would be to delete the TRIM function in cell A6 leaving just the edited text data in cell A4.

Entering the TRIM Function

Options for entering the function and its argument include:

  1. Typing the complete function: =TRIM(A4) into cell A6;
  2. Selecting the function and its arguments using the TRIM function dialog box.

The steps below use the TRIM function dialog box to enter the function into cell A6 of the worksheet.

  1. Click on cell A6 to make it the active cell - this is where the function will be located;
  2. Click on the Formulas tab of the ribbon menu;
  3. Choose Text from the ribbon to open the function drop down list;
  4. Click on TRIM in the list to bring up the function's dialog box;
  5. In the dialog box, click on the Text line;
  6. Click on cell A4 in the worksheet to enter that cell reference as the function's Text argument;
  7. Click OK to close the dialog box and return to the worksheet.
  8. The line of text Remove Extra Spaces from Between Words or Text should appear in cell A6, but with only one space between each word;
  9. If you click on cell A6 the complete function = TRIM ( A4 ) appears in the formula bar above the worksheet.

Pasting Over the Original Data With Paste Values

Steps to remove the original data and eventually the TRIM function in cell A6:

  1. Click on cell A6;
  2. Press the Ctrl + c keys on the keyboard or click on the Copy button on the Home tab of the ribbon - the selected data will be surrounded by the Marching Ants;
  3. Click on cell A4 - the location of the original data;
  4. Click on the small arrow at the bottom of the Paste button on the Home tab of the ribbon to open the Paste Options drop down menu;
  5. Click on the Values option in the drop down menu  - as shown in the image above - to paste the edited text into back into cell A4;
  6. Delete the TRIM function in cell A6 - leaving just the edited data in the original cell.

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.

If you have web page data with extra spaces that TRIM cannot remove, try this TRIM function alternative formula that may fix the problem.