# TRIM not working to remove space at end of cell value

#### screech

##### Active Member
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?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Are you sure it's actually a space?

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

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

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?

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?

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.

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)

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.

Replies
5
Views
611
Replies
6
Views
199
Replies
2
Views
307
Replies
6
Views
609
Replies
2
Views
871

1,219,580
Messages
6,149,109
Members
450,861
Latest member
metcala

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back