Macro to delete hidden characters in text

[Eric@SLR]

I need a technique or a macro that will clean spreadsheets of hidden characters in text (a common one that I deal with is the ALT+Enter to create another space in a cell for formatting purposes). I have tried the =trim () function - but it leaves these hidden characters alone - they are shaped like a square when they are revealed - but they still blend in very well. Help please

 

[Jack in the UK]

hi

The square you refer to is carrage return or EXCEL calls CHAR(10)

 

[Damon Ostrander]

Hi Eric,

Here is a macro that will "clean" a selected range on a worksheet of all control characters. You should put this into a macro module. Included is a helper function that it calls.

Sub CleanSelectedRange()
' "Cleans" contents of all selected cells on the active worksheet
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = CleanString(Cell.Value)
End If
Next Cell
End Sub

Function CleanString(StrIn As String) As String
' "Cleans" a string by removing embedded control (non-printable)
' characters, including carriage returns and linefeeds.
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character
Dim iCh As Integer
CleanString = StrIn
For iCh = 1 To Len(StrIn)
If Asc(Mid(StrIn, iCh, 1)) < 32 Then
'remove special character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh

End Function

 

[Legacy 98055]

Hi
Hi

Sub CleanerUpper()

Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False

End Sub

This is identical to the find/replace. Using code, you can force the carriage return value into the find replace command.
As is, you will need to run this from every sheet.

Good Day!
Tom

 

[Dave Hawley]

Hi Eric

Have you tried the CLEAN Function, eg

=CLEAN(A1)

Or to automate this try:

Sub CleanIt()
Dim rClean As Range

Set rClean = Range(Selection.Cells(1, 1), Selection.Cells(65536, 1).End(xlUp))
    rClean.EntireColumn.Insert
    rClean.Offset(0, -1).FormulaR1C1 = "=CLEAN(RC[1])"
    rClean = rClean.Value
End Sub

 

[lsut]

I was able to use the macro written by Damon,the problem is the formatting was not correct now. I would like to keep the formatting the same but not see the little box. Is this even possible?

 

[Damon Ostrander]

Hi Eric@SLR,

Sorry that this is coming about a year late, but just in case you (or others) are still interested, here is a solution that removes all those control characters <u>except</u> for linefeed characters. The line feeds enable the termination of a line giving spacing between lines. It is important to note that the linefeed characters will still show up as a rectangle if the cell is not formatted to allow text wrapping.

Here's the code:

Sub CleanSelectedRange()
' "Cleans" contents of all selected cells on the active worksheet
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = CleanString(Cell.Value)
End If
Next Cell
End Sub

Function CleanString(StrIn As String) As String

' characters, including carriage returns BUT NOT linefeeds.
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character

   Dim iCh  As Integer
   Dim Ch   As Integer      'a single character to be tested
   CleanString = StrIn
   For iCh = 1 To Len(StrIn)
      Ch = Asc(Mid(StrIn, iCh, 1))
      If Ch < 32 And Ch <> 10 Then
         'remove special character
         CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
      Exit Function
      End If
   Next iCh

End Function

You can use the CleanString function just as you would use the CLEAN worksheet function, or use CleanSelectedRange to clean all the cells in a selected range.

Damon

 

[Genki]

I modified CleanString() to optionally remove LineFeeds, too:

Function CleanString(StrIn As String, Optional IsRemoveLineFeeds As Boolean = False) As String
 
' removes invisible characters, including carriage returns. By default, LINEFEEDS are NOT removed.
' to REMOVE LINEFEEDS, pass TRUE
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character
 
  Dim iCh  As Integer
  Dim Ch   As Integer      'a single character to be tested
  CleanString = StrIn
  For iCh = 1 To Len(StrIn)
    Ch = Asc(Mid(StrIn, iCh, 1))
    If Ch < 32 Then
      ' remove Ch 10 only if option is True
      If (Ch <> 10) Or (Ch = 10 And IsRemoveLineFeeds) Then
        'remove special character
        CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
      End If
      Exit Function
    End If
  Next iCh
 
End Function