Question: I have just imported data into MS Excel. How do I remove leading or trailing spaces from the data? I also would like to limit the amount of space between words to one.
Answer: By using the TRIM function
Why: Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing
Applies To: Excel 2003, 2007, 2010
- For this example the screen shot given below will be used
2. Select cell B2 and type =Trim(A2) then press enter. Drag the formula down
3. The result will be as follows
As you can see from the data above, all spaces from the text except for single spaces between words have been removed.
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email [email protected] and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
