Solved

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

Posted on 2013-06-06
6
311 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

813 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

18 Experts available now in Live!

Get 1:1 Help Now