Tuesday, September 4, 2012

yogi_Clean Up Strings In Column C By Removing All Superfluous Characters


Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #731   Sep 04, 2012     www.energyefficientbuild.com.

user meph2u said: (http://productforums.google.com/forum/?zx=alk42vs49d24#!category-topic/docs/spreadsheets/vd_F4sx0R_E)
trimming trailing spaces.
I am getting external data "Lastname, first name,________" where the underscore signifies trailing spaces.   I need to take these off because of a lookup function.   I can find the TRIM function which seems to take of preceding spaces but have not been able to find a way to take of the trailing spaces.  Any ideas?
----
 I have put the problem in this spreadsheet on the TRIM tab:
https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdG8tT2xER2lfS0lYbUdvNUM3UVZaMnc#gid=1 When I copy the result of the trim function and past special in a third column, the original spaces persist.  I realize this is not exactly the question I asked, but it is a distinction I was not aware of until now.
I appreciate your help.
In this case, I don't think the SUBSTITUTE function will work as there are blanks in the middle of the names either after the comma, or before Jr, III, etc.

-------------------------------------------------------------------------------------------
I present solution in the following

2 comments:

  1. Am I wrong or does the TRIM function only remove leading and not trailing spaces?

    ReplyDelete
  2. Hi Robespierre:

    TRIM function removes both leading and trailing superfluous space characters.

    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete