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”. Let’s see how to handle this type of data.
- Get the ASCII code for the non-displayable characters.
- With the ASCII code, find what do the junk characters represent? (Carriage return, Line Feed, Page Break, End of File etc., )
- Determine whether to convert the junk to understandable format or to remove it.
Get ASCII Code
Either any Text editors with option to view the HEX or ASCII codes can be used or use the below code to get ASCII for each character in the file.
The below macro can be written in Excel VBE (Visual Basic Editor).
Visual Basic1234567891011121314151617181920212223242526Sub Read_Binary_Mode()'''''''Declare VariablesDim InputData As ByteInputFile = "D:\SampleFile.txt"InputFileNum = VBA.FreeFilenRow = 1'''''''Open the Input File in Binary ModeOpen InputFile For Binary As #InputFileNumWhile Not EOF(InputFileNum)'''''''Get the Binary Code or ASCII code from the FileGet #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 ModeSheets(1).Cells(nRow, 1) = InputData '''Binary DataSheets(1).Cells(nRow, 2) = VBA.Chr(InputData) '''Displayable CharacternRow = nRow + 1Wend'''''''Close the Input FileClose #InputFileNumMsgBox "Process Completed"End Sub
- Create file “Sample.txt” in D:\ Drive.
- Create a new Excel workbook and Press Alt + F11 to view VBE.
- Copy paste below code and Execute by pressing F5.
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.
Got the Code – Lets Decode its meaning
We got the file decoded. Google the decoded numbers and find what the actual Junk character in your file represent.
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.
Convert or Route to Trash
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.Visual Basic1234567891011121314151617181920212223242526Sub Write_Binary_Mode()'''''''Declare VariablesDim InputData As ByteInputFile = "D:\SampleFile.txt"OutputFile = "D:\SampleFileOut.txt"Junk_Val = 101 ''''' Character 'e' is removed just for example'''''''Open Input and Output File in Binary ModeInputFileNum = VBA.FreeFileOpen InputFile For Binary As #InputFileNumOutputFileNum = VBA.FreeFileOpen OutputFile For Binary As #OutputFileNumWhile Not EOF(InputFileNum)Get #InputFileNum, , InputData'''''''if it is Junk Value, dont write the data to FileIf InputData <> Junk_Val ThenPut #OutputFileNum, , InputDataEnd IfWendClose #InputFileNumClose #OutputFileNumMsgBox "Process Completed"End Sub
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.Visual Basic12345678910Sub Create_Ascii_Table()Dim nRow As DoubleFor nRow = 1 To 255Sheets(1).Cells(nRow, 1) = nRow '''''ASCII CodeSheets(1).Cells(nRow, 2) = VBA.Chr(nRow) '''''Corresponding CharacterNextMsgBox "Process Completed"End Sub
Previous Post: In 2 steps – How to remove #n/a in Excel? – Fixing Vlookup #n/a
Next Post: Add Sheets Dynamically to Excel
Remove Junk Data From Text File