# 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
###### Who is Participating?

x
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.

Commented:
Use this formula...

``````=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(127),"")))
``````

Saurabh...

Experts Exchange Solution brought to you by

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

Commented:
=Trim(Clean(SUBSTITUTE(Substitute(A1,CHAR(160)," "),CHAR(127)," "))
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
Commented:
No space is ok. But your trim function at the beginning takes care of the space too. So both of them is the same
Commented:
AndreasHermle,

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 " "

Saurabh...
Finance 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.
Finance 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.
Older 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.
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
Older 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.