Tip: The Trim Function – get rid of leading spaces
Get rid of those leading spaces and apostrophes
Have you ever imported data into Excel and discovered lots of leading spaces? Or leading Apostrophes? I know I have. This is not very useful as a leading spaces do not allow for effective sorting.
Here is how the Trim function can help to remove them.
In a column next to the data you wish to trim, type the function =Trim(
Click on the cell with the text that you wish to trim ( in my example the first few cells have a leading space and the last few have an apostrophe (which does not show)
Press Enter – the leading space or apostrophe is now gone
So you can see that the result shows in the cell (1) however in the background is still a formula (2)
Change the formula to show values
Having copied the formula to all the cells in the column, I now want to change the formula to values so that I can use the names in other ways.
Select the cells with the formula and copy them
Use Paste Special
Put your cursor where you want the new values to be (1)
Click on the drop down arrow under the paste icon (2)
Select Paste Values (3)
How is this tip going to save you time? Let me know how you will use it.
I had a case in a spreadsheet exported by our payroll software where I needed to use just the last 6 characters of a text field in a lookup. I used the RIGHT() function to get the 6 characters but sometimes the result would be blank (no error was indicated). This happened about 20 times throught the spreadsheet which was over 10 thousand rows. I used trim first and the RIGHT() function worked. Curious, I used the LEN() function to try to find out why the TRIM() function helped. Sure enough, in the cases where RIGHT() returned blanks, the length was 25 and most cells in the column the length was 9.