Trying to get rid of several non-visible characters using a macro

[zookeepertx]

Hello all!

I'm working on a macro for a friend's spreadsheet and have run into a snag.In column T, each cell contains either the following text:

2015-05-14 - ROSS: FWL NOTES
DEC FILED 5/13/15

or else appears to be blank.

However, I pasted them into Word and, in reality, there's a carriage return immediately after "NOTES" and 2 (or possibly more in some cells) paragraph marks after the date. We need to put 3 spaces after "NOTES", delete the carriage return and delete everything after the date. I tried copying the carriage return from Word and doing a Find/Replace, but it doesn't work.
In the "blank" cells there's actually a little circle that, I gather, is a non-blank space. Those need to go away. I've tried copying the character from Word and doing a Find/Replace; I've tried using Char160 and Char0160, using the number keypad, but I can't get rid of them!

(I did try copying the whole column and Paste Special/Values in a blank column and that did everything except getting rid of the carriage return, but that seems like a roundabout way to do it.)

I've just been trying to accomplish this directly on the sheet instead of a macro, just to try and get an idea of what I NEED to do in the macro, but, so far, I'm failing! Anybody got any solutions?

Thanks!

Jenny

[Tsjallie]

The Clean() function removes all non-printable (invisible) characters from a string.

[zookeepertx]

The Clean() function removes all non-printable (invisible) characters from a string.

Perfect! Never heard of that one before! Thank you!

The only trouble is that it doesn't seem to get rid of the Non-Blank Space in the cells that LOOK empty. That may not matter; I'll have to ask the lady that's going to use it, but I think she's gone for the day. Just in case, can we get rid of that too?

Jenny

[Tsjallie]

Not sure what you mean by "non-blank space".
Can you upload a sample containing cells with such content?

[zookeepertx]

Sure. This will be just a very basic sample, just so you can see how weird it is, LOL!
The cells in column A that LOOK blank, really aren't. If you copy the cell and paste it into Word and have it show the formatting marks, you can see the little circle that is (apparently) a "non blank space".
The cells that are occupied, notice in the formula bar how far spread apart the text is, even though it doesn't look like that in the cell. (If you paste into word, you'll see how strange those are, too.) But that part gets fixed using the Clean function you suggested.

Jenny

[Tsjallie]

The little circle are table column dividers. They come from pasting the cells into Word as is.
If you paste as text they're gone. Checked the cell length. It's 0.
So nothing to worry about. Clean() really cleanes the cell.
Text looking spread may come from propertional spacing most font have.

[zookeepertx]

Oh, well - DUHHH! I'm so silly, LOL! Thanks for catching that!

I appreciate your help! I learn something with every macro I work on, LOL!

Jenny

[Tsjallie]

Glad I could help. Thx for the rep.