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