Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way -
- It will remove non-printable characters with ASCII codes 0 to 31.
- It will remove leading and trailing blanks.
- Will remove characters with ASCII codes 127, 129, 141, 143, 144, 157, 160.
A good introduction to above character codes is at https://www.thoughtco.com/remove-non-printable-characters-3123798
The workbook containing the macro can be downloaded from Clean up Data Macro
1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.
Sub CleanUpData() Dim Ws As Worksheet Dim Rng As Range, Cell As Range Dim ArrCodes Dim i As Long Set Ws = ActiveSheet On Error Resume Next Set Rng = Ws.UsedRange.SpecialCells(xlConstants, xlNumbers + xlTextValues) If Rng Is Nothing Then Exit Sub End If On Error GoTo 0 ArrCodes = Array(127, 129, 141, 143, 144, 157, 160) Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each Cell In Rng 'Use the CLEAN function to remove 32 non printing chracters (0 to 31) 'Trim is for removing leading and trailing blanks Cell = Trim(WorksheetFunction.Clean(Cell)) 'Now remove character code 127, 129, 141, 143, 144, 157, 160 For i = LBound(ArrCodes) To UBound(ArrCodes) Cell = Replace(Cell, Chr(ArrCodes(i)), "") Next i Next Cell Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub