Remove CODE(7) or insert CODE(10)

[yky]

I have a list of phone numbers that contains the non-printable character CODE(7). I can copy and paste it to Find/Replace and find it. If I click at Find Next, I can see the active cell change from one cell to the other. However, Replace All doesn't do a thing. After clicking at Replace All button several times, I then click Find Next and Excel still finds them, all in their original cells. Is there a way to remove CODE(7) without using CLEAN()? CLEAN() will do the job but it also introduces another problem.

Below is one example from the list. The two numbers are in a cell.

09-977909921
09-5120584

I could use CLEAN() to remove the non-printable characters. Unfortunately, it'd also remove the linefeed character. The above example would then become this.

09-97790992109-5120584

Now, I need to insert the linefeed character to return the string back to the original. If I replace "09-" with "ALT001009-", it will do the job but also introduce a linefeed character to the beginning of the string. The net result is not just removing CODE(7) but also adding unwanted CODE(10). If I choose this route, I now need a way to remove linefeed in the beginning of a string.

I'm looking for a way to just remove CODE(7) without adding characters. Thanks.

 

[Yongle]

Try this method
First replace the linefeed character with ZZZ, then CLEAN, then substitute ZZZ with linefeed character
10 is also a linefeed character

Is it characher 13 ?
=SUBSTITUTE(CLEAN(SUBSTITUTE(A1,CHAR(13),"ZZZ")),"ZZZ",CHAR(13))
or character 10 ?
=SUBSTITUTE(CLEAN(SUBSTITUTE(A1,CHAR(10),"ZZZ")),"ZZZ",CHAR(10))

 

[yky]

Thanks.