Clean Function

[cassy269]

Hello - I need to "Clean" some data.

I have some nonprintable characters that I can use the clean functio
to remove. However, when I remove them it also removes the spac
between the two words in my text string. Is there a way to clean th
nonprintable characters and replace those characters with a spac
character instead of nothing

 

[Bernard Liengme]

Could be the hard space character CHAR(160) beloved of website coders

 

[RagDyeR]

Don't believe "Clean" removes Char(160).

Must be something else making those spaces.

 

[cassy269]

This is what happens...

John*Doe (with * being a non printable character)

Using the clean function removes the * and gives me this

JohnDoe

I want to have

John Doe

Can I do that?

Replace the nonprintable character with a space

 

[johamshason via OfficeKB.com]

Hey Cassy

Quite difficult to achieve. However if you have one or 2 nonprintable
characters then you can use the find and replace function.

ASsume: C20 has ja3@ja if @ is hte one which you wanna get rid off and get a
space. Then copy that @ and paste it into an empty cell say B18, use the
formula in c21 =REPLACE(C20,FIND(B18,C20),1," ")

This should give you the result: ja3 ja

PS: This would be good if you have few non printable characters which you can
make a note of.

Rgds
Parvez

 

[Dave Peterson]

If it's really that non-breaking space:

=substitute(a1,char(160)," ")
or
maybe...
=trim(substitute(a1,char(160)," "))

 

[cassy269]

Cant do find and replace as it is not a character that can be typed o
copied...
it is also not in the same place in each cell which eliminates th
possiblity of simply using left, right or mid functions :

 

[Dave Peterson]

Ps. Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Then you can use that info in the correct formula.

 

[Gord Dibben]

Cassy

If it is the 160 character you can replace it using Edit>Replace

What: use Alt + 0160(on numpad)

With: space


Gord Dibben MS Excel MVP