• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CLEAN ( ) to remove nonprinting characters

D

DashboardNovice

Member
In the attached file, the value in C5 has a nonprinting character at the end (more specifically a non-breaking space). In cell E5 I am applying the CLEAN( ) to the value in C5 to remove the nonprinting character.

The value in E5 was pasted to C12. I then used the RIGHT( ) in E12 to extract the last character from C12 to test if the nonprinting character is still there and it is still there. F12 has the ASCII code for the character in cell E12.

The CLEAN( ) is supposed to remove these nonprinting characters. Why didn't it remove it in my spreadsheet?
 

Attachments

  • Chandoo.org (CLEAN function).xlsx
    9.2 KB · Views: 7
DashboardNovice said:
The CLEAN( ) is supposed to remove these nonprinting characters. Why didn't it remove it in my spreadsheet?
Click to expand...
Not according to the Help for CLEAN() at https://support.microsoft.com/en-us...pid=xlmain11.chm60210&ui=en-us&rs=en-us&ad=us :
The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. For an example of how to remove these additional nonprinting characters from text, see Top ten ways to clean your data.

160 is not among them.
The end of that quote includes a link to other things you can do.
 
Thank you. It would be wonderful if the CLEAN( ) would just remove all nonprinting characters instead of just the first 32 nonprinting characters.
 
Not 100% sure, but the clean function inside PowerQuery does work better and might solve your issue. It has other powerfull functions to accomplish this as well.
Clearly the way forward for Microsoft when it comes down to transforming data, is PQ.
You could raise a uservoice on the Microsoft website and request clean to work for all Hidden chars.
 
Dashboardnovice, Good morning.

Try to use this formula: =Trim( Clean( Substitute( C5; CHAR(160); " " )))

Tell us if it worked for you.

I hope it helps.
 
You must log in or register to reply here.
Back
Top