Clean non printable characters and replace with space
 

When I use the formula to get rid of char 127 I want to replace it with a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7) by
using the SUBSTITUTE function, and then removes the BEL character from the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

=SUBSTITUTE(A5,CHAR(127)," ")

--
HTH,
Laura Cook
Neenah, WI


"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it with a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7) by
using the SUBSTITUTE function, and then removes the BEL character from the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

Can't you just use:

=SUBSTITUTE(A5,CHAR(127)," ")
=SUBSTITUTE(A5,CHAR(127),CHAR(32))

Biff

"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it with a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7) by
using the SUBSTITUTE function, and then removes the BEL character from the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

Tried that and it doesn't work because it doesn't recognize char 127. Only
when you add the clean function does it take out char 127 but I cannot figure
out how to replace it with a space.

"Laura Cook" wrote:

=SUBSTITUTE(A5,CHAR(127)," ")

--
HTH,
Laura Cook
Neenah, WI


"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it with a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7) by
using the SUBSTITUTE function, and then removes the BEL character from the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

Does not work see previous reply.

"T. Valko" wrote:

Can't you just use:

=SUBSTITUTE(A5,CHAR(127)," ")
=SUBSTITUTE(A5,CHAR(127),CHAR(32))

Biff

"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it with a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7) by
using the SUBSTITUTE function, and then removes the BEL character from the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

Oh, I see.

When I entered this FORMULA:

="MN"&CHAR(127)&987

It worked. However, when I type in MN<ALT 127987 it does not work.

This works (assumes the char 127 is always in the 3rd position):

=REPLACE(A5,3,1," ")

Biff

"rtremblay" wrote in message
...
Does not work see previous reply.

"T. Valko" wrote:

Can't you just use:

=SUBSTITUTE(A5,CHAR(127)," ")
=SUBSTITUTE(A5,CHAR(127),CHAR(32))

Biff

"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it with
a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7)
by
using the SUBSTITUTE function, and then removes the BEL character from
the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

That would be awesome but the boxes (char127) are mixed through out my data.
Thanks

"T. Valko" wrote:

Oh, I see.

When I entered this FORMULA:

="MN"&CHAR(127)&987

It worked. However, when I type in MN<ALT 127987 it does not work.

This works (assumes the char 127 is always in the 3rd position):

=REPLACE(A5,3,1," ")

Biff

"rtremblay" wrote in message
...
Does not work see previous reply.

"T. Valko" wrote:

Can't you just use:

=SUBSTITUTE(A5,CHAR(127)," ")
=SUBSTITUTE(A5,CHAR(127),CHAR(32))

Biff

"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it with
a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7)
by
using the SUBSTITUTE function, and then removes the BEL character from
the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

=SUBSTITUTE(A5,CHAR(10)," ")


"rtremblay" wrote:

When I use the formula to get rid of char 127 I want to replace it with a
space. Is there a way to modify the formula below to accomplish this.
Currently this takes char 127 out then replaces it with char 7 then the clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL
character (ASCII value of 127) with a BEL character (ASCII value of 7) by
using the SUBSTITUTE function, and then removes the BEL character from the
string "MN987" (MN987)

Clean non printable characters and replace with space
 

I can't reproduce your description.

ALT 127 (ascii) and CHAR 127 are different.

Char 127 is a "square box"
ALT 127 looks like "homeplate" (baseball)

You say that this works:

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7)))

Yet, if I use that formula on ALT 127 it fails. But you also say this fails:

=SUBSTITUTE(A5,CHAR(127)," ")

Seems to me that if the first formula works then the second formula should
also work. Are you sure this character is CHAR 127 and not ALT 127? ALT 127
evaluates to CHAR 63:

A1 = <ALT 127
B1: =CODE(A1) = 63
C1: =CHAR(B1) = ? (question mark)
D1: =FIND(CHAR(B1),A1) = #VALUE!

Change A1 to: =CHAR(127)
All the other formulas work

So, now I'm confused!

Biff

"rtremblay" wrote in message
...
That would be awesome but the boxes (char127) are mixed through out my
data.
Thanks

"T. Valko" wrote:

Oh, I see.

When I entered this FORMULA:

="MN"&CHAR(127)&987

It worked. However, when I type in MN<ALT 127987 it does not work.

This works (assumes the char 127 is always in the 3rd position):

=REPLACE(A5,3,1," ")

Biff

"rtremblay" wrote in message
...
Does not work see previous reply.

"T. Valko" wrote:

Can't you just use:

=SUBSTITUTE(A5,CHAR(127)," ")
=SUBSTITUTE(A5,CHAR(127),CHAR(32))

Biff

"rtremblay" wrote in message
...
When I use the formula to get rid of char 127 I want to replace it
with
a
space. Is there a way to modify the formula below to accomplish
this.
Currently this takes char 127 out then replaces it with char 7 then
the
clean
function removes char7.

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting
DEL
character (ASCII value of 127) with a BEL character (ASCII value of
7)
by
using the SUBSTITUTE function, and then removes the BEL character
from
the
string "MN987" (MN987)