Clever Geek Handbook

How to apply CLEAN () formula to whole Excel sheet?

I have an Excel file with almost 50 columns and 10,000 rows. They contain non-printable characters in some cells; I want to remove these characters from all cells in the sheet.

It would be tricky to apply the function CLEAN()

to every column. Is there any alternative?

+3


source to share


3 answers


I don't see it visually. If you see this character at the end of the cell text, you can find this value using the below code

debug.Print asc(right(range("D1").Value,1))

Just replace 110 with this value in the code below.



Sub Sample()
    Dim ws As Worksheet

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ws.Cells.Replace What:=Chr(110), Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

HERE is the ASCII table for your reference.

+4


source


To apply excel function CLEAN

for every cell of first 50 columns and 10,000 rows:



Sub cleanSheet()
    Dim r As Range
    Application.ScreenUpdating = False
    For Each r In Range(Cells(1, 1), Cells(10000, 50))
        r = WorksheetFunction.Clean(r)
    Next r
    Application.ScreenUpdating = True
End Sub
+1


source


You can just create another sheet, then in the first cell write a clean formula =Clean('sheet1'!A1)

and enter.

Now drag columns and rows. You will get the cleaned data on this sheet. You can also use the trim function in the same way.

Hope this helps!

0


source






More articles:


All Articles