Clean out nonprinting characters from cells

Dear Experts:

below formula cleans all nonprinting characters and other 'junk' data from a cell.

=Trim(Clean(Substitute(A1,CHAR(160)," ")))

I have learned that CHAR(127) is also a character that should be integrated in the formula.

I do not know how to nest the 'subformula' = SUBSTITUTE(B127,CHAR(127),""), into the above code

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this formula...


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
=Trim(Clean(SUBSTITUTE(Substitute(A1,CHAR(160)," "),CHAR(127)," "))
Andreas HermleTeam leaderAuthor Commented:
Dear both,

thank you very much for your very quick response.

your formulas are almost identical, with the exception of the last part, i.e. Saurabh got "" (nothing) and ExcelAddict got a " " (space) for the last argument.

Could you please clarify which one is the right one or does it not matter?

Regards, Andreas
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

No space is ok. But your trim function at the beginning takes care of the space too. So both of them is the same
Saurabh Singh TeotiaCommented:

What substitute formula does it replace the current value with revised value which you specify so since you want to replace it with non blank values you should give-->"" since " " is blank..

However the trim at the end will take care of whether you give "" or " "

Rob HensonFinance AnalystCommented:
TRIM will only deal with the " " if the " " ends up as a surplus space; ie leading, trailing or as a multiple space between words.

Replacing with "" will avoid that.
Rob HensonFinance AnalystCommented:
Is Char 160 a carriage/line feed? If so, I suspect you will want that replaced with " " as the contents after the carriage/line feed may not have a space and you will end up with last word of one line concatenated with first word of next line.
Martin LissOlder than dirtCommented:
This article which says the following describes the Clean function in detail. I added some bolding so you can see that there are other characters that you might consider. The article point to another article that contains some examples.

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

IMPORTANT   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 Remove spaces and nonprinting characters from text.
Andreas HermleTeam leaderAuthor Commented:
Dear all,

it is always hard to award points considering that all of you deserve the full amount.

Thank you very much for your great and professional help. I really appreciate it.

Regards, Andreas
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.