The TRIM function is very useful for cleaning all the spaces before and after a string in Excel. It is one of the Top 10 Excel functions you must know.
This function only requires one argument: the text you want to clean 😉
=TRIM(text)
When importing data into Excel, using this function is highly recommended to clean extra spaces in your cells. This is essential for ensuring data accuracy, such as when sorting the data. Extra spaces at the beginning or end of strings can disrupt sorting, leading to incorrect results.
Remove the spaces with the TRIM function
To clean up your data, use the formula: =TRIM(A2)
This function only removes spaces at the beginning or end of the text, not within it.
Transform the formulas into values
Once you use the TRIM function, it’s important to convert these formulas into values. Leaving them as formulas means that the original cells still contain the unwanted spaces. To keep only the cleaned results, use Paste Special and select “Values.”
Alternatively, use this trick with your mouse:
- Select your range of cells
- Right-click and drag the range to a new location
- Drag it back to the original location
- Release the right mouse button
- Select “Copy here as Values only”
