• 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.

    Also Read: Pivot Chart In Excel - How To Create A Pivot Table?

    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.

    Also Read: Excel VBA Create New Workbook - With Name - Copy Save Close

    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.


    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.

    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.

    Also Read: Consolidate Data - In Excel Files - Create Summary Report

    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.

    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.

    Also Read: Fibonacci Sequence Number Generator - Excel Easy Trick

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