Hi Excellers, and welcome to another blog post in my #Exceltips #formulafriday 2021 Excel series. Today, let’s look at how valuable a TRIM can be. The TRIM function is a really useful function to know in Excel. It is good to have in your Excel toolbox when you need to tidy up text data.
Essentially it will TRIM(!) all spaces from text except any single space between words. In other words, it will remove trailing spaces and extra spaces between words or other strings in Excel. However, Trim function in Excel will only remove the ASCII space character (32) from the text to get a bit more detailed.
If you have any Unicode characters (which can occur when there is an additional space character called the nonbreaking space character). This is commonly used in Web pages as the HTML entity. You may recognize 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.
TRIM Function Syntax.
=TRIM(TEXT)
where
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.
Working Though A TRIM Function Example.
Let’s work through an example. Enter the following text including the extra spaces into Cell C1.
Please remove these extra characters 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 formula bar above 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 characters as seen by Excel) really can impact your data analysis resulting in non-matches when you expect matches.
Other Data Tidy Up Tips.
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 What’s Next?
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series. Click on the link below
How To Excel At Excel – Formula Friday Blog Posts.
