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.
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:
- 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.
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”.