Solved

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

Posted on 2013-06-06
6
307 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

706 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

17 Experts available now in Live!

Get 1:1 Help Now