TRIM not working to remove space at end of cell value

[screech]

Hello,

I am trying to remove an extra space at the end of a text string in my database. I have searched the message boards and found that "TRIM" would be useful. I have tried this formula but it doesn't work.

I am making a new column (column B) next to my original data and using =TRIM(A1) as an excel formula. Then when I copy column B and paste values of this column, the extra space remains.

The extra space is always at the end of the text string. Is there another way to write some VBA code to remove the last character of the value of the cell if it is not a letter a-z?

 

[Norie]

Are you sure it's actually a space?

It could be some other non-printing character that TRIM can't deal with.

 

[screech]

Yeah, it's probably not a normal "space". But whatever it is, I would like it gone.

 

[screech]

And now that I'm looking at the data, it appears that the "spaces" between words in the cell are not really spaces either, and that will be my next problem. What is this character, and can I convert it to a normal space?

 

[Norie]

screech

I have no idea what the character is, I haven't seen your data and I can't really make a guess as I don't know where it's come from.

PS I do actually have an idea what it might be (CHAR(160)), but do you get my point?

 

[screech]

I should have let myself more time to think. I copied this "space-that-is-not-a-space" and used the find/replace option to convert it to a space. Whatever it was, I think this got rid of it. Thanks for the quick replies, Norie.

 

[Norie]

screech

Can you copy the space-that-is-not-a-space to a blank cell?

Then put this into another blank cell, changing A1 to the reference to the cell you copied to.

=CODE(A1)

 

[Aladin Akyurek]

Try...

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

for CHAR(160) is more often than not the culprit.

Another means would be to run cleaning options fron the free ASAP Utilities add-in.