How to trim spaces (extra spaces at the end or double spaces between words) using the TRIM function.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Trim spaces from cells
The TRIM function removes all spaces from a cell except for a single space between words & can be very useful when you pull information from other systems, which may or may not add additional characters or more especially spaces.
In this example you’ll see we have some part numbers & using SUMIF we count how many a’s there are, how many b’s, c’s etc, & you’ll see there’s a total of ten & our SUMIF’s are coming to 10. What happens however, if let’s say for example in this cell here, we look here, we add a simple space. When I click enter you’ll see that that space afterwards has an impact on the SUMIF because ccc as it is not considered the same as ccc space. And this can often happen when you are pulling information from other systems that spaces get added on at the end. The TRIM function can help us with this, so here we’ve got the exact same set up but instead of taking information as is, we can use the TRIM function, so when I go to the FUNCTION WIZARD, and I find TRIM, say ok. The text where the text is that you want to trim, we click on it, say ok, & now when I copy it down, you’ll see that here where we had the ccc with the space, the TRIM function is removing it, as a result the SUMIF works better & actually pulls through all the correct information. This is just a nice way of to remove any spaces that could cause problems later in your spreadsheet.