Excel Trim function is very useful function that removes extra spaces from data. This formula removes all spaces from data except for single space between words. While validating data on the basis of length of cell, the trim function can give you valid length by removing spaces from beginning, spaces from end and extra spaces between words.
This can be useful for refining raw data received from any external application with irregular spacing.
Syntax And Examples:
Trimming extra spaces from plain text:
=TRIM("text")Trimming extra spaces from text in a cell:
=TRIM(Cell Reference)| A | |
| 1 | Excel Bee |
| 2 | |
| 3 | =TRIM(A1) |
| 4 |
Result in A3: Excel Bee
We have kept two spaces between “Excel” and “Bee” in A1 cell and the trim function has trimmed extra spaces and gave output with single space left between words.
Extra Tips:
- Trim function only trim 7-bit ASCII space character (value 32).
- Trim function does not trim line break (7-bit ASCII line break character (value 10).
- If you are unable to trim leading or trailing extra spaces, then you should consider checking ASCII code of the character by using function =CODE( ).
Watch Video Tutorial
Priya Bansal is a seasoned education writer with a passion for creating insightful and engaging content. With a background in education and a knack for research, she excels at producing articles, blogs, and resources that inform and inspire. Priya’s work contributes to the enrichment of educational discourse and empowers learners of all ages.
