Remove Junk Characters In Text File – Excel Trim Just Not Helping

Excel Trim Not Working – Remove Unwanted Characters

Is Your Excel function TRIM not able to remove non-displayable characters?

A Trim function will remove only the spaces at start & end of a text. Very less people know that there are other good functions available in Excel to remove special characters. Some of these special characters visible & few are just invisible.

Whether it is displayable or non-displayable characters, It is better to know its ASCII code, before we delete it. In addition to cleaning junk characters from text, we will learn here about:

1. Formula: Clean non-printable Characters in Excel.
2. Formula: Replace or Substitute a junk character in Worksheet.
3. VBA: To Remove Special Character in String
4. VBA: To Remove Junk Character from Text file.
5. VBA: To Get ASCII code for non-displayable junk characters.
6. VBA: To create ASCII Table in Excel

Before removing any character, find what do the junk characters represent (Carriage return, Line Feed, Page Break, End of File etc., ) & then proceed.

1. Quick Way To Remove Junk Characters in Excel

This is a easy function available in Excel in addition to TRIM. The function name & syntax is:

=CLEAN(text)

It is the same for TRIM also. “=TRIM(text)”. This function just removes most of the non-printable characters from Excel cell or a string variable. This is a self explanatory function & hence no more explanations about it.

If you are looking to use this inside the VBA code, then invoke this command in this method. “Application.WorksheetFunction.Clean(StringVariable)”

2. Replace/Substitute Special Characters in Excel

If TRIM & CLEAN functions did not work out, then this technique explained below is the ultimate one.

=(SUBSTITUTE(<Reference or Text>,CHAR(141),””)

With this method, not only the special non-printable characters, but also you can replace any unwanted character within a string.

3. VBA Code To Remove Special Characters From String

This is a Excel VBA Programming code that can remove junk values from a string field. Inside the code an array of ASCII codes for non-printable character are defined. Add any code that is missing in the array for your case, if your problem is not solved.

Function RemoveJunk(ByVal sInp As String) As String
    Dim idx As Long, ArrayAscii As Variant
  
    'Define Array for ASCII codes for Non-Printable Characters
    ArrayAscii = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
                        11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, _
                       31, 127, 129, 141, 143, 144, 157)

    'Loop Thru Each Element in Array & Verify whether Any Special Character appears in String
    For idx = LBound(ArrayAscii) To UBound(ArrayAscii)
    If InStr(sInp, Chr(ArrayAscii(idx))) Then
        sInp = Replace(sInp, Chr(ArrayAscii(idx)), "")
    Next
  
    'Return String After removing Junk Values
    RemoveJunk = Application.WorksheetFunction.Trim(Application.WorksheetFunction.Clean(sInp))
End Function

This code is taken from this link & modified.

4. How To Remove Junk Characters from Text File?

Once you have decided to replace or remove the Junk values, use the below code. It actually opens the input file in Binary mode. If it finds the ASCII code to be removed, it skips to next character without writing the junk character.

Sub Write_Binary_Mode()
    'Declare Variables
    Dim InputData As Byte
    InputFile = "D:\SampleFile.txt"
    OutputFile = "D:\SampleFileOut.txt"
    Junk_Val = 13 'Character CR - Carriage Return is removed - just for example

    'Open Input and Output File in Binary Mode
    InputFileNum = VBA.FreeFile
    Open InputFile For Binary As #InputFileNum
    OutputFileNum = VBA.FreeFile
    Open OutputFile For Binary As #OutputFileNum

    While Not EOF(InputFileNum)
        Get #InputFileNum, , InputData

        'if it is Junk Value, dont write the data to File
        If InputData <> Junk_Val Then
            Put #OutputFileNum, , InputData
        End If
    Wend

    Close #InputFileNum
    Close #OutputFileNum
    MsgBox "Process Completed"
End Sub

We got the file decoded. Google the decoded numbers & find what the actual Junk character in your file represent. Or refer the ASCII table created in below code.nn

If that Junk values is something that can be removed, use the below code.

Note: Before using the below code, make a back up of the input file just in case if something unexpected error happens.

5. VBA To Get ASCII Code Of Text Characters

A Text editor with option to view the HEX or ASCII codes can be used get ASCII for each character in a Text file. Alternatively, use the below vba code to display ASCII codes for any character.

The below macro can be written in Excel VBE (Visual Basic Editor). But, before that:

1. Create file “Sample.txt” in D:\ Drive.
2. Create a new Excel workbook and Press Alt + F11 to view VBE.
3. Copy paste below code and Execute by pressing F5.

Sub VBA_Read_Binary_Mode_Get_ASCII_Characters()
    'Declare Variables
    Dim InputData As Byte
    InputFile = "D:\SampleFile.txt"
    InputFileNum = VBA.FreeFile
    nRow = 1

    'Open the Input File in Binary Mode
    Open InputFile For Binary As #InputFileNum
    While Not EOF(InputFileNum)

        'Get the Binary Code or ASCII code from the File
        Get #InputFileNum, , InputData

        'If you Input File has Huge Volume of data then
        'Instead of Writting it to the Worksheets, then
        'Write it to a Text file in Normal Write Mode
        Sheets(1).Cells(nRow, 1) = InputData            'Binary Data
        Sheets(1).Cells(nRow, 2) = VBA.Chr(InputData)   'Displayable Character
        nRow = nRow + 1
    Wend

    'Close the Input File
    Close #InputFileNum
    MsgBox "Process Completed"
End Sub

This code will open the “Sample.txt” file in Binary Read mode, convert the characters into the equivalent ASCII code and then write the results in sheet1.

6. Building ASCII Table in Excel VBA Programming

You can make your own ASCII code Table for numbers from 1 to 255. Use the below code and it will give the code and its corresponding value in sheet1.

Sub Create_Ascii_Table_In_Excel()
    Dim nRow As Double

    For nRow = 1 To 255
        Sheets(1).Cells(nRow, 1) = nRow          'ASCII Code
        Sheets(1).Cells(nRow, 2) = VBA.Chr(nRow) 'Corresponding Character
    Next

    MsgBox "Process Completed"
End Sub

What is a Junk Value?

If we refer any English dictionary they would suggest these equivalent words – scrap, useless, debris, garbage.

But in general, if the computer does not display a data in readable format, we tend to name it as Junk character. But not always these Junk characters are “useless”.