TRIM is a really really useful function to know in Excel and one you should definitely have in your Excel tool kit.
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.
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 TRIM and break it down.
=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 for clarity. Enter the following text including the extra spaces into Cell C1 of a n new work sheet.
Please remove these extra characters from Excel
Here is how to use TRIM
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 and return the expected value, but sometimes those extra little spaces (which are actually characters as seen by Excel) really can really impact your data analysis. This can result in non matches when you expect matches. Technically it is correct as it is not a match even though sometimes those extra little characters are really hard to spot.
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.
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.
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 TRIM and break it down.
=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 for clarity. Enter the following text including the extra spaces into Cell C1 of a n new work sheet.
Please remove these extra characters from Excel
Here is how to use TRIM
- 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 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 and return the expected value, but sometimes those extra little spaces (which are actually characters as seen by Excel) really can really impact your data analysis. This can result in non matches when you expect matches. Technically it is correct as it is not a match even though sometimes those extra little characters are really hard to spot.
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.
BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com
a site that shares Excel tips and tricks with it's enthusiastic
members. To join in the discussion, where users are working smarter and
faster with Excel sign up for the FREE newsletter and as a bonus receive
a FREE EBook- 50 Top Tips and Tricks.
Aucun commentaire:
Enregistrer un commentaire