This tip was printed from the Tips for Spreadsheets website at http://tipsforspreadsheets.com/.

Microsoft Excel 2003 Functions and Formulas - Trim Extra Spaces from Text

When copying and pasting text from another application into a Microsoft Excel 2003 spreadsheet, or in other circumstances where entered data may not meet desired specifications, text may contain extra spaces inside and surrounding the words.

For example, if you have a spreadsheet containing a list of names and plan on displaying those names via functions in other cells, it may prove useful to remove extra spaces else the function results may appear awkward.

=trim(VALUE)

VALUE is the value you wish to trim.

For example, assume the following spreadsheet:


   A                   
1     Bill    Connor  
2   Sarah     Jones 
3     John   Williamson  
4         Laura  Smith   
5  
6  =TRIM(A1)
7  =TRIM(A2)
8  =TRIM(A3)
9  =TRIM(A4)

In cells A1 - A4, the names contain extra spaces before, in the middle of, and afterwards.

Cells A6 - A9 would show the following cleaned-up values:

A6: Bill Connor
A7: Sarah Jones
A8: John Williamson
A9: Laura Smith

Press the "print" button on your browser or select "File" - "Print" to print this tip. Then, return to Microsoft Excel 2003 Functions and Formulas - Trim Extra Spaces from Text.

Standard disclaimer applies - Read http://tipsforspreadsheets.com/copyright-disclaim.html.