| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I replace a BEL Character of 7 with a space | Excel Discussion (Misc queries) | |||
| How do you find and replace tab characters in a cell in Excel? | Excel Discussion (Misc queries) | |||
| FIND and REPLACE characters needed | Excel Worksheet Functions | |||
| Formula to replace invalid filename characters | Excel Worksheet Functions | |||
| How can I replace 2 of 5 characters within an cell in MS Excel? | Excel Worksheet Functions | |||
Linear Mode
