Remove all Non-Printable characters as well as Clean and Trim; leave name text intact with spaces

[blbat]

I saw Rick Rothstein's code that removed Non-Printable Characters, and did a Clean and trim as well.

Can it be modified to leave spaces in the middle alone?
such as, if I have a column of names that may have leading or trailing spaces, or both...Like so:

Alfred E. Newman

John Paul Jones

I'd want the leading spaces removed, as well as the trailing spaces, but Leave the Names intact

Rick Rothstein's Code:

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

 

[HackSlash]

Just use trim by itself.

myCleanString = Trim(sourceString)

https://www.techonthenet.com/excel/formulas/trim.php

 

[Rick Rothstein]

I saw Rick Rothstein's code that removed Non-Printable Characters, and did a Clean and trim as well.

Can it be modified to leave spaces in the middle alone?
such as, if I have a column of names that may have leading or trailing spaces, or both...Like so:

Alfred E. Newman

John Paul Jones

I'd want the leading spaces removed, as well as the trailing spaces, but Leave the Names intact

Unless your names have multiple contiguous space within them, my code already does this. This line from my code...

CleanTrim = WorksheetFunction.Trim(S)

removes leading and trailing spaces and collapses multiple contiguous internal spaces down to single spaces. If you want to preserve multiple contiguous internal spaces while removing leading and trailing spaces, then do as HackSlash suggests and use Trim instead of WorksheetFunction.Trim...

CleanTrim = Trim(S)