Often time, you may copy and paste text from the web, other sources to excel. Then, you need to do comparison between strings. In Excel cells, two strings may look similar but when you do the comparison, they are not.
It is because you may have leading or trailing spaces. The solution would be to remove the spaces before pasting. However, that would be a very tedious task. You may need to spend a lot of time doing so.
There is a better solution for this problem. In fact, Excel has a built in function to deal with this situation. It is called TRIM.
Let’s look how it works.
As you can see, the two text strings on the left most look identical. However, their lengths are different. It is because the string on the above row has two more trailing spaces than the one below it. After apply TRIM() function to the two strings, you will see they have the same length.
That’s how you eliminate the the leading and trailing spaces from cells in Excel. This trick works well in all versions of excel, from Excel 1997 to Excel 2016 (the newest version of Excel at the time I write this post). There is no doubt that this function will work well in future versions of Excel.
I hope you find this article useful. If it helped you, please don’t forget to share. Also, please check out my excel autocomplete from list add-in here. It will make your job as data entry expert much easier.