Trim Formula not working

[Statz]

For some reason the trim formula is not working. There is a space after every word in this document. I'm trying to remove that space. I did the trim formula then copied down. Then I copied paste special to a new column & the space is still showing up.

Thanks

[MarvinP]

Hey Statz,

I'd bet those things that look like spaces are really character 160. Do a replace all of them with spaces and try again.

Just looked at your workbook.. Perhaps you need to turn on your calculation mode to automatic? On the Formulas Tab look at Calculations Options.

[JeteMc]

If you are removing spaces between words also try this:

Please Login or Register  to view this content.

Let me know if you have any questions.

[TMS]

It's a non-breaking space, not a space. Use

Please Login or Register  to view this content.

[Statz]

Hey Statz,

I'd bet those things that look like spaces are really character 160. Do a replace all of them with spaces and try again.

Just looked at your workbook.. Perhaps you need to turn on your calculation mode to automatic? On the Formulas Tab look at Calculations Options.

I think you're right about them not being spaces. I went ahead and deleted the "space" then did a few more spaces and the trim formula works.

But what is character 160? Not sure what you're referring to there.

Thanks!!

[MarvinP]

Hey Statz,

I find those character 160 in a lot of the old mainframe generated text docs. Some old software just threw out a 160 instead of spaces. I'm clueless on why...
Read:
http://www.adamkoch.com/2009/07/25/w...character-160/ or
http://www.utexas.edu/learn/html/spchar.html

[TMS]

See post #4. I see this a lot in data that has been downloaded from web sites.

[Statz]

Hey.TMS.

I tried your formula and it looks like it worked for almost all but not some. I attached a file and highlighted the issues in red.

Thanks

EDIT:I did copy this from a web page btw. Might be why there is an issue.

[TMS]

Please Login or Register  to view this content.

and copy down

[Statz]

Please Login or Register  to view this content.

and copy down

Thank you for doing this. Did you just have to use trim instead of the 1st formula? just kinda curious why.

Also, in my file I attached in post 8. I was going to apply filters at the top so I can sort in descending/ascending order to see the "0". But because of the count if formula I have it never works correctly. Any idea why this happens?

Thank you!

[TMS]

Well, I guess some values end in a normal space, which is fixed with TRIM. Others end in a non-breaking space (character code 160) which isn't. Others probably have neither. So, we use SUBSTITUTE to sort out the CHAR(160) and TRIM to remove the real trailing space(s). As shown, you can just combine the functions.

As for the COUNTIF, no idea.

[Statz]

Thanks again! And everybody who chimed in.

[TMS]

You're welcome. Thanks for the rep.