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?
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.
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
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!