Clean/Trim Macro Length Limitation

Hey, guys!

I found a macro on the web that does a trim/clean on selection of cells - see below. But the thing is it does NOT work on cells which are longer than roughly 1 sentence or so. Meaning as soon as there's e.g. roughly 2 sentences in a cell or more - it replaces cell's contents with #VALUE (instead of doing trim/clean).

Could any one advice how to fix this?

Please Login or Register  to view this content.

Originally Posted by BadlySpelledBuoy

Could you provide a sample workbook with some data where this throws up the error? I've tried it on data with 10 and 20 sentences in some cells and it works fine for me.

BSB

There's a sample right HERE (the attach file here does not work for me).

Basically what I did is took the very same macro as above, and the two sentences from my initial post. When macro is run it replaces the 2 sentences in the cell with #VALUE. I tested it on 2 computers, result's the same.

But don't get me wrong, it might as well be that it runs perfectly on your end and only doesn't work for me - all good. But I'd love to understand how to fix it, so it works for me as well.


I'd really appreciate any advice on this :]

Originally Posted by BadlySpelledBuoy

Put simply, if your text strings are over 255 characters long then this method will fail.

BSB

Absolutely, I did figure out that issue/limitation's indeed - length. Any chance you might be able to suggest a way to adjust the macro to fix this issue (=remove length limitation)?

Originally Posted by jindon

Change to

Please Login or Register  to view this content.

That works like a charm! Thank you so, so, so very much, Jindon!!

And purely for posterity, the final, working macro is below.

Please Login or Register  to view this content.