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)