Clean, Trim, or otherwise remove all non-printing characters?

[diversification]

Hi there,
This topic has been brought up a few times, but the solutions I've found are not working too well. All I want to do is be able to remove all non-printing characters (including outliers like CHAR 160) and not have to worry about it breaking functions (primarily MATCH.) I'm not entirely clear on why neither TRIM or CLEAN handles these outliers; from one article it sounds like it's values 127, 129, 141, 143, 144, 157 and 160, but I suspect there might be others too.

Anyway, I'm wondering if anyone has a solution that might work the way I guess I would have assumed that CLEAN would work, by removing *all* off these characters. This solution from a similar thread seemed like it might work, but after trying it out, it just returns a #NAME? error. CLEAN() not dependable to remove non-printable characters?

Ideally, I'd be looking for something that doesn't chew up a ton of processing power, as I may be using it on very large spreadsheets.

 

[Joe4]

Anyway, I'm wondering if anyone has a solution that might work the way I guess I would have assumed that CLEAN would work, by removing *all* off these characters. This solution from a similar thread seemed like it might work, but after trying it out, it just returns a #NAME? error. CLEAN() not dependable to remove non-printable characters?

The #NAME error usually means that it does not recognize the name, so you probably placed the VBA code in the wrong spot.
Create a NEW blank module in VBA in your workbook, and copy/paste the custom function from that other link there.
Then you should be able to call from anywhere within your workbook (on any sheet itself or from VBA).

 

[diversification]

The #NAME error usually means that it does not recognize the name, so you probably placed the VBA code in the wrong spot.
Create a NEW blank module in VBA in your workbook, and copy/paste the custom function from that other link there.
Then you should be able to call from anywhere within your workbook (on any sheet itself or from VBA).

I wonder if I had to save, close and re-open the workbook to get it going perhaps? Regardless, after deleting the 1st entry, entering it again, saving the workbook, closing, then re-opening, suddenly it's working now.

Out of curiosity, do you have any idea why Microsoft hasn't made this CleanTrim function the standard operation for the Clean and Trim functions? Seems strange that all those characters would just be ignored.

Anyway, this seems like it's probably a suitable solution. Thanks!

 

[Joe4]

Anyway, this seems like it's probably a suitable solution. Thanks!

You are welcome.

Out of curiosity, do you have any idea why Microsoft hasn't made this CleanTrim function the standard operation for the Clean and Trim functions? Seems strange that all those characters would just be ignored.

I have no idea. My only thought is that the special characters are usually things that come from other programs or web downloads, and not things that you would typically see of data entered directly in to Excel.