Getting Rid of Non-Printing Characters Intelligently

Re. the TextClean macro described in my earliest comment below, I forgot to mention that formatting of individual characters among text constants in the selected range remains undisturbed even if some characters are removed by the macro. Here is an abbreviated stand-alone version that retains support for Undo (Ctrl+Z):

Public Sub TextClean()
    Text_Proc "TextClean"
End Sub

Private Sub Text_Proc(Optional Proc As String = "Undo")
    Const myName As String = "Text_Proc"
    Static rText As Range, sSave() As String 'Static
    Dim rCell As Range, sText As String, sChar As String, sConv As String
    Dim sCase As String, sXtra As String, bUndo As Boolean, nSave As Long
    Dim nChar As Integer, nConv As Integer, n As Integer
    bUndo = (Proc = "Undo")
    If bUndo Then
        If rText Is Nothing Then Exit Sub
        rText.Select
        rText.Cells(1).Show
        DoEvents
    Else
        On Error Resume Next
            'Intersect is necessary in case Selection.Cells.Count = 1
            Set rCell = Intersect(Selection, _
                Selection.SpecialCells(xlCellTypeConstants, xlTextValues))
        On Error GoTo 0
        If rCell Is Nothing Then
            sText = "There are no text constants in Selection"
            MsgBox sText, vbCritical, Proc
            Exit Sub
        End If
        Set rText = rCell
        rText.Select
        rText.Cells(1).Show
        sText = "ASCII characters 0 thru 31 (Tab, CR, LF, ...) will be " _
            & "removed from the selected text. Enter additional " _
            & "characters to remove (case-sensitive) including Alt+nnnn " _
            & "for UNICHAR(nnnn). ASCII space will be ignored. " _
            & "Click Cancel to skip additional characters."
        sXtra = InputBox(sText, Proc)
        sXtra = Replace(sXtra, " ", "")
        ReDim sSave(1 To rText.Cells.Count)
    End If
    nSave = 0
    For Each rCell In rText
        With rCell
            nSave = nSave + 1
            If bUndo Then
                sText = sSave(nSave)
                sConv = .Value
            Else
                sText = .Value
                sSave(nSave) = sText
                sConv = Application.WorksheetFunction.Clean(sText)
                For n = 1 To Len(sXtra)
                    sConv = Replace(sConv, Mid(sXtra, n, 1), "")
                Next n
            End If
            nChar = 1
            nConv = 1
            For n = 1 To Len(sText)
                sChar = Mid(sText, n, 1)
                sCase = Mid(sConv, nConv, 1)
                If sChar = sCase Then
                    nConv = nConv + 1
                    nChar = nChar + 1
                ElseIf bUndo Then
                    .Characters(nChar, 0).Insert sChar
                    nChar = nChar + 1
                Else
                    .Characters(nChar, 1).Delete
                End If
            Next n
        End With
    Next rCell
    If bUndo Then Exit Sub
    Application.OnUndo ("Undo " & Proc), (ThisWorkbook.Name & "!" & myName)
End Sub

@Mike J
Thank you for your question. It depends how you implement My Excel Toolbox.
1. The easiest way is to download the add-in file MyToolbox.xlam from https://sites.google.com/view/MyExcelToolbox and install it as described in the last two paragraphs of the Description section on the Home page. In that case macros can be run by pressing Ctrl+M (not Ctrl+Shift+M) to open the macro named MyToolboxMacros.
2. If you only want to implement the M_Text module containing the TextClean macro, you will discover it depends on other modules in My Excel Toolbox. M_Text requires M_Support and M_Miscellaneous; the latter requires M_WinAPI, F_Lightbox, F_MyToolboxMacros, F_MyToolboxShortcuts, F_PasteSpecial, and F_ProgressBar. Therefore, you must insert those four standard modules plus five user form modules into your VBA project.
3. If you only want to implement the TextClean macro, you need to copy/paste TextClean, Text_Proc, Title, ProperX, and ProperY from M_Text plus Selection_OK from M_Miscellaneous.
If you have further questions about My Excel Toolbox, please use the Comment page at https://sites.google.com/view/MyExcelToolbox

@J.Woolley
Have I implemented your MyToolbox incorrectly?
I am trying to run TextClean() from VBE, using F5 after selecting a range, but I am getting "Compile error: Variable not defined". It seems to be xlErrSpill in Public Function ErrorAsText() in a different module. If I comment out the Case xlErrSpill test, the problem repeats for xlErrCalc. After commenting out this one the macro works perfectly.
It seems it is because I am using excel 2010, but commenting out these 2 case statements probably means it may not function correctly in later versions. How can I get it to work with v2010 and also v2021, which I am about to use on another computer?

The TextClean macro in My Excel Toolbox removes ASCII characters 0 through 31 (Tab, CR, LF, etc.) from text constants in the selected range of cells. Additional characters can be specified for removal (case-sensitive) including Alt+nnnn for UNICHAR(nnnn). The macro supports Undo (Ctrl+Z).
See https://sites.google.com/view/MyExcelToolbox/

Hello,

Again, if the user is routinely receiving file in a defined form (such as CSV) I suggest that they build a Power Query to do the incoming data cleaning. That's exactly what Power Query is designed to do.

Thanks,

Mark