Macro to delete hidden characters in text
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro to delete hidden characters in text

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi

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



    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eric

    Have you tried the CLEAN Function, eg

    =CLEAN(A1)

    Or to automate this try:

    Code:
    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

  6. #6
    New Member
    Join Date
    Aug 2010
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete hidden characters in text

    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?

  7. #7
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete hidden characters in text

    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 except 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:

    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
    Last edited by Damon Ostrander; Aug 11th, 2011 at 01:53 PM.

  8. #8
    New Member
    Join Date
    Jan 2011
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete hidden characters in text

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

    Code:
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com