Remove ASCII Character #127 in Excel

Each character on a computer - printable and non-printable - has a number known as its Unicode character code or value.

Another, older, and better known character set is 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 is identical to the ASCII set.

Many of the first 128 Unicode characters are referred to as control characters and they are used by computer programs to control peripheral devices such as printers.

As such, they are not intended for use in Excel worksheets and can cause a variety of errors if present. Excel's CLEAN function will remove most of these non-printable characters - with the exception of character #127.

of 03

Unicode Character #127

Remove ASCII Character #127 from Data in Excel
Remove ASCII Character #127 from Data in Excel. © Ted French

Unicode character #127 controls the delete key on the keyboard. As such, it is not intended to ever be present in an Excel worksheet.

If present, it is displayed as a narrow box-shaped character - as shown in cell A2 in the image above - and it was probably imported or copied accidentally along with some good data.

Its presence may:

  • cause simple formatting problems in a worksheet;
  • affect the sorting and filtering of data;
  • cause calculation problems if present in a cell along with data that is being used in a formula.
of 03

Removing Unicode Character #127

Even though this character cannot be removed with the CLEAN function, it can be removed using a formula containing the SUBSTITUTE and CHAR functions.

The example in the image above shows four rectangle-shaped character along with the number 10 in cell A2 of an Excel worksheet.

The LEN function - which counts the number of characters in a cell - in cell E2 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.

Cell A3 contains the SUBSTITUTE/CHAR formula 


to replace the four #127 characters from cell A2 with nothing - (shown by the empty quotation marks at the end of the formula).

As a result

  1. the character count in cell E3 is reduced to two - for the two digits in the number 10;
  2. 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 does the actual replacing while the CHAR function is used to tell the formula what character to replace.

of 03

Removing Non-Breaking Spaces from a Worksheet

Similar to non-printable characters is the non-breaking space (&nbsp) which 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, so 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 with a formula that combines the SUBSTITUTE, CHAR, and TRIM functions.