Remove ASCII Character #127 in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

Remove ASCII Character #127 in Excel

Get rid of non-printable characters in Excel worksheets

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on July 16, 2020 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook The Excel CLEAN function removes most non-printable characters except for character #127. To remove character #127 in Excel, use a special formula containing the SUBSTITUTE and CHAR functions. The information in this article applies to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, and Excel for Mac.

What Is Unicode Character #127

Unicode character #127 controls the Delete key on the keyboard. It appears as a narrow, rectangle-shaped symbol. This character can sometimes erroneously appear when you copy and paste data in Excel. The presence of character #127 can cause several issues, including: Formatting problems in a worksheet. Data sorting and filtering issues. Calculation problems with data that is used in a formula.

How to Remove Unicode Character #127

In the example Excel worksheet below, cell A2 contains four rectangle-shaped characters along with the number 10. The LEN function, which counts the number of characters in a cell, shows that cell A2 contains six characters (the two digits for the number 10 plus the four boxes for character #127). Due to the presence of character #127 in cell A2, the addition formula in cell D2 returns a #VALUE! error message. To turn the data in cell A2 into a calculable value, set up the following SUBSTITUTE/CHAR formula in a different cell (as seen in cell A3): =SUBSTITUTE(A2,CHAR(127),"")
The formula replaces the four #127 characters from cell A2 with nothing (represented by the empty quotation marks at the end of the formula). As a result: The character count in cell E3 is reduced to two for the two digits in the number 10.The addition formula in cell D3 returns the correct answer of 15 when adding the contents for cell A3 + B3 (10 + 5). The SUBSTITUTE function handles the replacement. The CHAR function tells the formula which characters to replace.

Remove Non-Breaking Spaces From a Worksheet

Similar to non-printable characters, the non-breaking space ( ) can also cause problems with calculations and formatting in a worksheet. The Unicode code number for non-breaking spaces is #160. Non-breaking spaces are used extensively in web pages. If data is copied into Excel from a web page, non-breaking spaces may show up in a worksheet. Removing non-breaking spaces can be done using a formula that combines the SUBSTITUTE, CHAR, and TRIM functions.

Unicode vs ASCII Characters

Each character on a computer (printable and non-printable) has a number known as its Unicode character code or value. Another older character set known as ASCII, which stands for the American Standard Code for Information Interchange, has been incorporated into the Unicode set. As a result, the first 128 characters (0 to 127) of the Unicode set are identical to the ASCII set. Many of the first 128 Unicode characters are referred to as control characters because these are used by computer programs to control peripheral devices such as printers. These characters are not intended for use in Excel worksheets and can cause a variety of errors if present. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use Excel's Clean Function How to Combine the ROUND and SUM Functions in Excel How to Use the COUNTIF Function in Excel How to Use the Round Function in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use the Excel MID Function Use the Excel RIGHT Function to Extract Characters How to Use the Excel DATE Function How to Use Excel's MROUND Function Ignore Error Values When Finding the Average in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Excel TRUNC Function Round up Numbers in Excel With the ROUNDUP Function How to Add Numbers in Excel Using a Formula How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT Relative, Absolute, and Mixed Cell References in Excel and Sheets Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies