Solved

Clean / Trim Cells also gets rid of manual line breaks (Alt + Enter)

Posted on 2013-06-06
6
309 Views
Last Modified: 2013-06-09
Dear Experts:

below macro performs 'cleaning and trimming' on selected cells.

The trouble is that manually entered 'manual line breaks' (Alt+Enter) get also deleted. As a matter of fact, I would like to preserve these.

How?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Clean_Trim()

Application.EnableCancelKey = xlDisabled
     
     '// CLEAN > Removes all nonprintable characters from text.
     '// TRIM > Removes all spaces from text except for single
        
    Dim CleanTrimRg As Range
    Dim oCell As Range
    Dim Func As WorksheetFunction
     
    Set Func = Application.WorksheetFunction
     
    On Error Resume Next
    
     
    If Err Then MsgBox "No data to clean and Trim!": Exit Sub
     
    For Each oCell In Excel.Selection     
    
    With oCell
         If .HasFormula = False Then
           oCell = Func.Clean(Func.Trim(oCell))
        End If
    End With
    Next
     
End Sub

Open in new window

0
Comment
Question by:AndreasHermle
  • 2
  • 2
  • 2
6 Comments
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 200 total points
ID: 39225168
Hi Andreas,

I suggest line 23 could be changed from:

oCell = Func.Clean(Func.Trim(oCell))

to read:

oCell = Replace(Func.Clean(Func.Trim(Replace(oCell, Chr$(10), "xx_temp_xx"))), "xx_temp_xx", Chr$(10))

or even:

oCell = Replace(Func.Clean(Func.Trim(Replace(oCell, vbLF, "xx_temp_xx"))), "xx_temp_xx", vbLF)


This is assuming that the text "xx_temp_xx" is not part of any cell value already.

If it is, then please change the two occurrences in the revised line to something unique.

BFN,

fp.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39225398
As an alternative approach
Sub Clean_Trim()

   Application.EnableCancelKey = xlDisabled

   '// CLEAN > Removes all nonprintable characters from text.
   '// TRIM > Removes all spaces from text except for single

   Dim lRow                   As Long
   Dim lCol                   As Long
   Dim n                      As Long
   Dim Func                   As WorksheetFunction
   Dim vData
   Dim vCell

   Set Func = Application.WorksheetFunction

   vData = Excel.Selection.Formula

   For lRow = LBound(vData, 1) To UBound(vData, 1)
      For lCol = LBound(vData, 2) To UBound(vData, 2)
         If InStr(vData(lRow, lCol), vbLf) > 0 Then
            vCell = Split(vData(lRow, lCol), vbLf)
            For n = LBound(vCell) To UBound(vCell)
               vCell(n) = Func.Clean(Func.Trim(vCell(n)))
            Next n
            vData(lRow, lCol) = Join(vCell, vbLf)
         Else
            vData(lRow, lCol) = Func.Clean(Func.Trim(vData(lRow, lCol)))
         End If
      Next lCol
   Next lRow
   Excel.Selection.Formula = vData
End Sub

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 300 total points
ID: 39225585
Revision in case of array formulas:
Sub Clean_Trim()
   Dim rConstants             As Range
   Dim rCell                  As Range

   With Application
      .EnableCancelKey = xlDisabled
      .ScreenUpdating = False
   End With

   On Error Resume Next
   Set rConstants = Excel.Selection.SpecialCells(xlCellTypeConstants)
   On Error GoTo 0

   If Not rConstants Is Nothing Then

      For Each rCell In rConstants.Cells
         rCell.Value = CleanString(rCell.Value)
      Next rCell

   End If

   With Application
      .EnableCancelKey = xlInterrupt
      .ScreenUpdating = True
   End With

End Sub
Function CleanString(sIn As String) As String
   Dim Func                   As WorksheetFunction
   Dim n                      As Long
   Dim vCell
   '// CLEAN > Removes all nonprintable characters from text.
   '// TRIM > Removes all spaces from text except for single

   Set Func = Application.WorksheetFunction

   If InStr(sIn, vbLf) > 0 Then
      vCell = Split(sIn, vbLf)
      For n = LBound(vCell) To UBound(vCell)
         vCell(n) = Func.Clean(Func.Trim(vCell(n)))
      Next n
      CleanString = Join(vCell, vbLf)
   Else
      CleanString = Func.Clean(Func.Trim(sIn))
   End If

End Function

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:AndreasHermle
ID: 39228435
Dear both,

sorry for the delay in getting back to you.

Looks very professional as always. Will do some testing soon and let you know. Thank you very much for your great support.

Regards, andreas
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39228468
Thanks Andreas.

No need to apologise :)
0
 

Author Closing Comment

by:AndreasHermle
ID: 39233478
Dear both,

great job from both of you. Both codes work just fine. Rorya's code is a little bit more 'elegant' therefore I'd like to award a couple of more points to him.  

Again, thank you very much for your great professional help. I really appreciate your professionalism and your time taken.

Regards, Andreas
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now