I want to strip / clean() a cell of everything except CHAR(015), but ASCII Control Characters can't SUBSTITUTE() out

Question

I'm looking to take a text string and remove everything except the carriage returns and normal text (so other control characters, spaces, bullet points and a few other bits of junk). However

 CLEAN(A1) 

strips the carriage returns too.

I've tried a string of nested

 SUBSTITUTE(A1, CHAR(000), "")
 SUBSTITUTE(A1, CHAR(001), "")

etc

but it keeps defaulting to

 SUBSTITUTE(A1, CHAR(0), "")
 SUBSTITUTE(A1, CHAR(1), "")

and giving me an error. Any help?

Not very experienced in VBA, though I could gather some basics with a bit of googling.

Answer 1

Assuming that you want to remove:

• ASCII characters 0 through 9
• ASCII characters 11 and 12
• ASCII characters 14 through 32
• ASCII characters 127 through 255

then consider the following User Defined Function:

Public Function JunkKiller(S As String) As String
    Dim temp As String, a
    ary = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255)
    temp = S

    For Each a In ary
        temp = Replace(temp, Chr(a), "")
    Next a
    JunkKiller = temp
End Function

This will retain both ASCII-10 and ASCII-13, if you also want to retain the space character, just remove the 32 from the Array() statement.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=JunkKiller(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 2

Carriage returns are Char(13), which is 015 in octal. But you probably also want Char(10) which is line-return.

Nested substitutes do work, but awkwardly:

=...Substitute(Substitute(Substitute(A1,Char(1),""),Char(2),""),Char(3),"")...

You'll need to substitute Char(1) to Char(31) omitting Char(10) and Char(13).

A saner method might be to make use of the \n newline code and the \r carriage return code:

1. Substitute Char(10) for \n
2. Substitute Char(13) for \r
3. Clean what's left
4. Substitute \n for Char(10)
5. Substitute \r for Char(13)

This would fail if your input contains \n or \r, obviously.

=SUBSTITUTE(SUBSTITUTE(CLEAN(SUBSTITUTE(SUBSTITUTE(B1,CHAR(13),"\r"),CHAR(10),"\n")),"\r",CHAR(13)),"\n",CHAR(10))

There, only 4 Substitutes instead of 29!