YTLE#74: CLEAN & TRIM functions to fix Bad Data

Hi ....

I have tried to trim, clean and substitute data. However, it seems that the function does not work on these data.

I attached the samples data in this thread. The data to be cleaned is in column A

Advice and help is very much appreciated. Thanks.

hey,

i wanna to trim and clean column with out making new column then copy

here is my formula

=vlookup("Jan",trim(clean(A1:A15)),3,0)

how can i write this formula correctly

Dear friends below macro work fine for me.I'm taking data form my system to excel.with that data some unwanted spaces also there.so i need some modification in this macro....
Code:

 Sub  MG31Jan41
 Dim  Rng  As  Range
 Dim  Dn  As  Range
Application.ScreenUpdating = False
 Set  Rng = Range(Range("C1"), Range("C" & rows.Count).End(xlUp))
 With  CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
 For   Each  Dn  In  Rng
     If  Not .Exists(Dn.value)  Then 
        .Add Dn.value, Nothing
     Else 
        Dn = "": Dn.Offset(, 1) = ""
     End  If
 Next 
 End  With
Application.ScreenUpdating = True
 End   Sub 

i want to add clean & trim function to this code..pls help me.....
Code:

 Else
        Dn = "": Dn.Offset(, 1) = ""

Regards: http://www.excelforum.com/excel-prog...g-a-macro.html

This is very handy, but I am getting a #VALUE! for character strings longer than 256. I do not understand why, but is there a way around this?

Hia...

I've written some code that selects all the text cells in a spreadsheet and cleans up the contents with Trim, Clean:

Range("A1", Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeConstants, 2).Select

For Each rng In Selection
rng.Value = Application.WorksheetFunction.Clean(Trim(rng.Value))
Next rng

Is there a faster way of doing this becaise some worksheets have a lot of data?

Cheers...A

Excel 2007, how do you write the clean or trim command into a spreadsheet? I am trying to remove the leading space in a column and excel help menu is no help.

Where do i type =trim or =clean in the cell above the data or in the cell with the data?

Header: col A Row 1= Supervisor
Data: Col A Row 2-2,000 = Supervisor's name list (with a leading space)

I have this VBA to format my spreadhseet. The last item that I need to add in is to Trim the Leading, excessive, and Trailing Spaces from the sheet. I also need to Clean the non printable characters from the spreadsheet.

I would like to have this be completed right at the begining before I have the autofit of the columns.

Code:

Sub BorrowerMasterArm()
    Application.ScreenUpdating = False
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.ColumnWidth = 25
    Cells.EntireColumn.AutoFit
    Selection.RowHeight = 12.75
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("O:S").Insert Shift:=xlToRight
    Columns("W:Z").Insert Shift:=xlToRight
    Columns("AB:AF").Insert Shift:=xlToRight
    Columns("AH:AL").Insert Shift:=xlToRight
    Dim End_Row As Long
    End_Row = Range("A" & Rows.Count).End(xlUp).Row
    Range("O2:O" & End_Row).FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])"
    Range("P2:P" & End_Row).FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))"
    Range("Q2:Q" & End_Row).FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))"
    Range("R2:R" & End_Row).FormulaR1C1 = "=RIGHT(RC[-4],4)"
    Range("S2:S" & End_Row).FormulaR1C1 = "=LEN(RC[-5])"
    Range("W2:W" & End_Row).FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])"
    Range("X2:X" & End_Row).FormulaR1C1 = "=RIGHT(RC[-2],2)"
    Range("Y2:Y" & End_Row).FormulaR1C1 = "=MID(RC[-3],5,2)"
    Range("Z2:Z" & End_Row).FormulaR1C1 = "=LEFT(RC[-4],4)"
    Range("AB2:AB" & End_Row).FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])"
    Range("AC2:AC" & End_Row).FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))"
    Range("AD2:AD" & End_Row).FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))"
    Range("AE2:AE" & End_Row).FormulaR1C1 = "=RIGHT(RC[-4],4)"
    Range("AF2:AF" & End_Row).FormulaR1C1 = "=LEN(RC[-5])"
    Range("AH2:AH" & End_Row).FormulaR1C1 = _
        "=IF((RC[4]=1),"""",CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3]))"
    Range("AI2:AI" & End_Row).FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))"
    Range("AJ2:AJ" & End_Row).FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))"
    Range("AK2:AK" & End_Row).FormulaR1C1 = "=RIGHT(RC[-4],4)"
    Range("AL2:AL" & End_Row).FormulaR1C1 = "=LEN(RC[-5])"
  Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("N1").Select
    Selection.Copy
    Range("O1").Select
    ActiveSheet.Paste
    Columns("W:W").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("V1").Select
    Selection.Copy
    Range("W1").Select
    ActiveSheet.Paste
    Columns("AB:AB").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("AA1").Select
    Selection.Copy
    Range("AB1").Select
    ActiveSheet.Paste
    Columns("AH:AH").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("AG1").Select
    Selection.Copy
    Range("AH1").Select
    ActiveSheet.Paste
    Columns("N:N").Delete Shift:=xlToLeft
    Columns("O:R").Delete Shift:=xlToLeft
    Columns("Q:Q").Delete Shift:=xlToLeft
    Columns("R:T").Delete Shift:=xlToLeft
    Columns("R:R").Delete Shift:=xlToLeft
    Columns("S:V").Delete Shift:=xlToLeft
    Columns("S:S").Delete Shift:=xlToLeft
    Columns("T:W").Delete Shift:=xlToLeft
    Rows("1:1").Insert Shift:=xlDown
    Range("B1").Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
    Rows("1:1").Delete Shift:=xlUp
    Range("A1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Columns("A:A").Select
    Selection.NumberFormat = "##-#######"
    Columns("D:D").Select
    Selection.NumberFormat = "000-00-0000"
    Columns("M:M").Select
    Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
    Columns("O:O").Select
    Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
    Columns("P:P").Select
    Selection.NumberFormat = "0.00000"
    Columns("W:W").Select
    Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
    Columns("AC:AC").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=10
    Columns("AO:AO").Select
    Selection.NumberFormat = "0.00000"
    Columns("CB:CB").Select
    Selection.NumberFormat = "0"
    Columns("CV:CX").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("EJ:EJ").Select
    Selection.NumberFormat = "$#,##0.00"
    Rows("1:1").Select
    Selection.NumberFormat = "General"
    Columns("EK:IV").Delete Shift:=xlToLeft
    Rows("2000:65536").Delete Shift:=xlUp

    Dim lastrow As Long
lastrow = [A65536].End(xlUp).Row
For i = lastrow To 1 Step -1
If Cells(i, 1) = "" Then Rows(i & ":" & i).EntireRow.Delete
Next i
    Dim lLastRow As Long, lLastColumn As Long
    Dim lRealLastRow As Long, lRealLastColumn As Long
     
     
    With ActiveSheet.UsedRange
        lLastRow = .Rows.Count
        lLastColumn = .Columns.Count
    End With
     
     
    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
     
    Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow + 1, 1)).EntireRow.Delete
    Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn + 1)).EntireColumn.Delete
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    'LastRow = Range("A65536").End(xlUp).Row
    For i = lastrow To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("CR:CR"), Range("CR" & i).Value) > 1 Then
            Range("CR" & i).Interior.ColorIndex = 8 ' or  Range("CR" & i).ClearContents   for only blank Column A
        End If
        ActiveSheet.UsedRange 'Resets LastCell
        Next
   Application.ScreenUpdating = True
    Range("CR1").Select
End Sub

Also, If there is anything that can be cleaned up in this code to help it run faster would be appreciated. I have used a combination of Macro record and adding in code from other examples for the operations that I want to have completed.

Thanks
Rodney Jorgensen

Hello,

I wanted to do the equivalent of Clean(Trim(.)) on a list of cells from A1:A1000 using a macro. Does someone have an idea of how to achieve that?

thanks in advance,
Andy