Excel treats all characters in text, including spaces, as a value, so if you have two words or text strings that look identical but one has a space at the end Excel will consider these to be two different texts.
For example:
“Hello ” (Trailing space)
"Hello” (No trailing space)
Because spaces at the end of text are not easy to identify with the naked eye it can be hard to know from looking at a dataset if some words or strings that look the same are actually identical.
This is where the TRIM function can be really useful to help clean up datasets that are messy even when they don't look it.
Download File
If you would like to follow along download the attachment below.
What is the TRIM function?
The TRIM function in Excel removes:
Leading spaces e.g. " Trim Example"
Trailing spaces e.g. "Trim Example "
Extra spaces between words e.g. "Trim Example"
This can help clean datasets to ensure that data is consistent and formatted correctly.
The syntax of the function is simple and only requires one input:
=TRIM(text)
text: This is the text string which you want to remove any leading, trailing or extra spaces.
Why use the TRIM function?
Excel considers spaces before and after text to be part of the text itself.
If you have trailing spaces (spaces after the last letter) then it can be very difficult to see this
One way to check this is to double click on the cell, or select the cell and press F2 to see where the cursor is.
Remove trailing and leading spaces with TRIM
Using the TRIM function it is easy to remove these trailing spaces, as well as leading spaces e.g. spaces at the start of a word.
As the TRIM function has only one criteria, text, just select the cell that you want to trim.
Removing extra spaces with TRIM
TRIM can also be used to remove extra spaces between words.
The TRIM function only removes extra spaces (more than one) between words leaving a single space, it does not remove single spaces between words.
Using TRIM to clean a dataset
When downloading data to Excel from some external sources the formatting can sometimes have inconsistent trailing spaces.
It can be hard to notice this until you start seeing errors in calculations from the data.
The TRIM function can be used to clean the range in A2 to A13 so that all three North values are consistent.
Nest TRIM in other functions
The TRIM function can also be nested in other functions so they treat text with leading, trailing or extra spaces as trimmed values.
Conclusion
The Trim function can be used to remove trailing, leading and extra spaces in text in Excel.
When applied across a range it can be copied and pasted as a value to remove spaces from an entire dataset.
It can also be nested in other functions to correct text used by that function.
Opmerkingen