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:
- Formula: Clean non-printable Characters in Excel.
- Formula: Replace or Substitute a junk character in Worksheet.
- VBA: To Remove Special Character in String
- VBA: To Remove Junk Character from Text file.
- VBA: To Get ASCII code for non-displayable junk characters.
- 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:
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.
123456789101112131415161718Function RemoveJunk(ByVal sInp As String) As StringDim idx As Long, ArrayAscii As Variant'Define Array for ASCII codes for Non-Printable CharactersArrayAscii = 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 StringFor idx = LBound(ArrayAscii) To UBound(ArrayAscii)If InStr(sInp, Chr(ArrayAscii(idx))) ThensInp = Replace(sInp, Chr(ArrayAscii(idx)), "")Next'Return String After removing Junk ValuesRemoveJunk = 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.1234567891011121314151617181920212223242526Sub Write_Binary_Mode()'Declare VariablesDim InputData As ByteInputFile = "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 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
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:
1234567891011121314151617181920212223242526Sub VBA_Read_Binary_Mode_Get_ASCII_Characters()'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.
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.12345678910Sub Create_Ascii_Table_In_Excel()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
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”.
Remove Junk Characters In Text File – Excel Trim Just Not Helping