Hello - I need to "Clean" some data.

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


--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

CLEAN doesn't remove spaces. Presumably what you think are spaces are really
unprintable characters.

Dave
--
Brevity is the soul of wit.


"cassy269" wrote:


Hello - I need to "Clean" some data.

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


--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

Could be the hard space character CHAR(160) beloved of website coders
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
CLEAN doesn't remove spaces. Presumably what you think are spaces are
really
unprintable characters.

Dave
--
Brevity is the soul of wit.


"cassy269" wrote:


Hello - I need to "Clean" some data.

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


--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

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

Must be something else making those spaces.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bernard Liengme" wrote in message
...
Could be the hard space character CHAR(160) beloved of website coders
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
CLEAN doesn't remove spaces. Presumably what you think are spaces are
really
unprintable characters.

Dave
--
Brevity is the soul of wit.


"cassy269" wrote:


Hello - I need to "Clean" some data.

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


--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

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?


--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

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

cassy269 wrote:
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

--
cassy26

Posted from - http://www.officehelp.i

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200701/1

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


--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

Cassy

If it is the 160 character you can replace it using EditReplace

What: use Alt + 0160(on numpad)

With: space


Gord Dibben MS Excel MVP

On Thu, 18 Jan 2007 23:58:04 +0530, cassy269
wrote:


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

If it's really that non-breaking space:

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


cassy269 wrote:

Hello - I need to "Clean" some data.

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

--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

--

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.

Dave Peterson wrote:

If it's really that non-breaking space:

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

cassy269 wrote:

Hello - I need to "Clean" some data.

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

--
cassy269
------------------------------------------------------------------------
cassy269's Profile: http://www.officehelp.in/member.php?userid=6142
View this thread: http://www.officehelp.in/showthread.php?t=1323006

Posted from - http://www.officehelp.in

--

Dave Peterson

--

Dave Peterson