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(Trim1

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)

Trim2

Press Enter – the leading space or apostrophe is now gone

trim3

 

 

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.

trim4

Select the cells with the formula and copy them

Use Paste Special

Trim5

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.

Shelley Fishel

 

 

 

 

 

 

 

 

 

 

Related Posts Plugin for WordPress, Blogger...
One Response to "Tip: The Trim Function – get rid of leading spaces"
  1. Paul K Dick says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge
 
Textbox Section
 

Contact us

Would you like to talk through your IT Training needs? Just send through your details and we'll get back to you.