Explanation

TheSUBSTITUTE functioncan find and replace text in a cell, wherever it occurs.

How can you figure out which character(s) need to be removed, when they are invisible?

For more general cleaning, see theTRIM functionand theCLEAN function.

Excel formula: Clean and reformat telephone numbers

CHAR can insert characters that are hard to enter into a formula.

CODE Function

The Excel CODE function returns a numeric code for a given character.

For example, =LEFT(“apple”,3) returns “app”.

Excel formula: Strip non-numeric characters

How to use CHAR and CODE functions

Excel formula: Strip html from text or numbers

Excel formula: Remove leading and trailing spaces from text

Excel SUBSTITUTE function

Excel CHAR function

Excel CODE function

Excel LEFT function

Excel CLEAN function

Article image