trimming cells

[rlocher]

ok, there is probably an easy solution here, but I can't figure it out. I need to remove the trailing "spaces" in some cells but I don't think they're really spaces. TRIM or CLEAN doesn't work. I tried LEN(SUBSTITUTE(A1," ","")) to count the number of characters without spaces and it's counting something other than text. Text to columns (space or tab delimited) doesn't work - it separates by spaces between words, but not the ones after. I still have blank space after the text. First question, if they're not spaces, what are they? Secondly, how do I trim the unwanted junk after the text? Of note, these are city/town names so spaces need to be kept between words - just not after. Any ideas?

 

[rlocher]

great! I made a new sheet and you'll be able to see the result of the some of the things we tried. The cells in question are in column A.

http://www.box.net/shared/b8ez14pb40

I really appreciate you taking a look.

 

[VoG]

I just ran the TrimAll macro and it removed the trailing spaces. Here is the result http://www.box.net/shared/5xnyxobc1y

Note that you have to select all the cells to be trimmed then run the macro.

 

[rlocher]

well oddly the TrimAll macro worked for me once it was cut/pasted into a new sheet. But still does not on the original report that was sent to me. I'm not going to try to figure that one out - I'll just work around it!

Thank you so much. I really appreciate your help.

 

[VoG]

The TrimAll code has to go in a regular module, not a worksheet's code module. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste in the code.

 

[rlocher]

got it. thank you.