The TRIM function is very useful for cleaning all the spaces before and after a string
- The TRIM function needs only one argument
It's the text to clean
- Convert the formula into a value
However, after applying the TRIM function to the cells, you must convert the formulas into values.
=TRIM(text)
When importing data into Excel, using this function to 'clean' extra spaces in your cells is strongly recommended. The function is very simple, requiring only one argument: your text to be cleaned.
In this example, sorting the data doesn't return a correct result because of the extra spaces at the beginning of some cells.
Remove the spaces with the TRIM function
So, we must remove the spaces at the beginning of all the cells.
=TRIM(A2)
Only the spaces at the beginning or at the end of the string are removed.
Transform the formulas into values
Of course, you can't retain the formulas because it implies that the original cells still contain blanks. To keep only the results, convert the formulas into values by using 'Paste Special' and selecting 'Values'.
Alternatively, there is also the trick with the mouse.
- Select your range of cells
- Move the range with the right button of the mouse
- Return to the previous location
- Release the button on the mouse
- Select the option Copy here as Values only
