Excel TRIM function

Summary

The TRIM function in Excel is designed to remove all extra spaces from text, except for single spaces between words. It is especially useful for cleaning up text from external sources or web content, which often contains irregular spacing.
Syntax
				
					=TRIM(text)
				
			
  • text: The text from which you want to remove spaces.
Return value
Returns a text string with irregular and extra spaces removed.

How to use

To use TRIM, input the text or a cell reference containing the text as the argument. TRIM effectively removes all spaces from text except for single spaces between words. This includes eliminating leading, trailing, and multiple consecutive spaces within the text. However, TRIM does not remove non-breaking space characters often found in web content.

Examples

Simple TRIM
Removing Extra Spaces from Text:
				
					=TRIM(" Hello World! ")

				
			
This formula returns “Hello World!” by removing leading, trailing, and extra spaces between words.
TRIM with Cell Reference
Applying TRIM to Cell Content:
				
					=TRIM(A2)

				
			
If cell A2 contains ” Data Analysis “, this formula returns “Data Analysis”.
TRIM with LEFT
Combine TRIM with other text functions for complex formulas. For example, using TRIM with LEFT:
				
					=LEFT(TRIM(D2), 5)

				
			
This extracts the first five characters from the cleaned text in D2.

Additional Notes

  • TRIM removes all spaces from text except for single spaces between words.
  • It does not remove non-breaking space characters (ASCII 160) often found in web data.
  • In data analysis, TRIM is used to avoid mismatches or errors caused by unseen spacing issues.
  • TRIM can be effectively combined with other functions for advanced text manipulation.

Related Functions

Excel CLEAN function

The Excel CLEAN function removes non-printable characters from text, ensuring data cleanliness for processing and analysis.

Content Navigation