I have a utility 'cleaning' sub that I can run on a Range.
I made it into a UDF which is not as efficient as a Sub run on multiple cells
Option Explicit
Function CleanLeftString(S As String, N As Long) As String
Dim c As Long, i As Long
Dim aryChars As Variant
'The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII
'code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting
'characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove
'these additional nonprinting characters
S = Application.WorksheetFunction.Clean(S)
'replace CR, NL, and tab with space, and 127, 129, 141, 143, 144, 157 and 160 with space
aryChars = Array(9, 13, 10, 127, 129, 141, 143, 144, 157, 160)
For i = LBound(aryChars) To UBound(aryChars)
If InStr(S, Chr(aryChars(i))) > 0 Then Call Replace(S, Chr(aryChars(i)), " ", xlPart)
Next i
'WS TRIM() removes multiple spaces, VBA.Trim does not
If InStr(S, Chr(32)) > 0 Then S = Application.WorksheetFunction.Trim(S)
CleanLeftString = Left(S, N)
End Function
Edit - Added Left()