Happy Friday every body TGIF…..time for some Formula Fun.
TRIM is a really really useful function to know in Excel.
Essentially it will TRIM(!) all spaces from text except any single spaces between words. In other words it will remove trailing spaces and extra spaces from between words or other strings in Excel. To get a bit more detailed, Trim will only remove the ASCII space character (32) from text.
If you have any Unicode characters (which can occur when there is an additional space character called the nonbreaking space character ), commonly used in Web pages as the HTML entity. You may recognise this as   character depending on where you have received your data from.
So if you have downloaded or received data from webpages, and you see  , TRIM will not remove these types of characters.
As usual let’s look at the syntax of the formula today
=TRIM(TEXT)- the text argument being the text form which you want the spaces removed.
The TEXT argument can contain the actual data enclosed in quotation marks, it can also be a cell reference to the location of the text data in the worksheet.
Let’s work through an example. Enter the following text including the extra spaces into Cell C1.
Please remove these extra charaters from Excel
- Click on cell D1 – this is where our formula will sit.
- Click Formulas Tab
- Choose Text from the ribbon to open the function drop down list.
- Click on TRIM in the list to bring up the function’s dialog box
- In the dialog box, click on the Text line
- Click on cell D1 in the spreadsheet.
- Click OK.
- The line of text Remove Extra Spaces in Excel should appear in cell D1 but with only one space between each word.
- When you click on cell D1 the complete function = TRIM ( D1 ) appears in the formual bar bove the worksheet.
Extra spaces are removed. So when do you think you would use this TRIM function??
I have used it when VLOOKUPs actually look they should work but sometimes those extra little spaces (which are actually charaters as seen by Excel) really can impact your data analysis resulting in non matches when you expect matches.
Also, to tidy up inherited, downloaded or imported data I usually run some data cleansing methods over which TRIM is part of usually in combination with the LEN function. If you want to read more about using LEN you can read my blog posts here and here.
Want Even More Formula Fun?