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?

 

[VoG]

Try selecting the range and running this http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[rlocher]

thank you, but unfortunately, this is did not work either.

 

[VoG]

Copy one of these spaces (just the space) and paste it into a spare cell (say H1). What does this return

=CODE(H1)

 

[rlocher]

I get #VALUE!

 

[VoG]

Hmm. You would get that if H1 was blank. Maybe you didn't manage to copy the space (it can be tricky).

In H1 enter

=RIGHT(A1,1)

where A1 has text with a trailing space. What does the CODE formula return now.

 

[rlocher]

I think you were right.

Now I'm getting 32

 

[VoG]

Curiouser and curiouser.

Code 32 is a regular space so I don't understand why none of the methods that you've tried have worked.

Edit

Can you try

=LEFT(A1,LEN(A1)-1)

Does that remove the space.

 

[rlocher]

I don't suppose there a way to upload my sheet so you can see it?

 

[VoG]

You can upload it to www.box.net and provide the link in this thread.