clear all trailing char(32)

[joemeshuggah]

I have a list of names that have are all of the same length (51) due to a string of char 32 trailing the last letter...for example, if the name Joe were in cell A1, len(a1) returns a value of 51, due to there being 48 additonal characters after the e (all being char 32).

I need to be able to replace ONLY the trailing char 32 characters to get a vlookup to work...nothing I do seems to work. Any ideas?

 

[VoG]

Try

=TRIM(A1)

 

[Jose_Chasez]

TRIM should remove the Char(32)s.

 

[joemeshuggah]

trim does not work; even after the trim the length remains as do the char(32)

 

[VoG]

In that case they are not CHAR(32) but probably CHAR(160).

Try selecting the cells then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[cb12]

Also if they were char(160) (or anything else) and all followed the end of the name, and you don't want to use VBA you could modify this

=LEFT(A1,FIND(CHAR(160),A1,1)-1)

 

[joemeshuggah]

char(160) was the culprit...cb12's solution worked quite nicely...Thanks!

 

[Scott Huish]

In a list of names, CHAR(160) is probably not in the middle of the names, so you could probably just use a SUBSTITUTE function.

=SUBSTITUTE(A1,CHAR(160),"")