How to remove special characters in Excel

A big challenge that many face when cleaning data imported from other applications is the removal of special characters. When cleaning up data or performing data analysis, you may need to remove all the special characters from a cell or cells. Removing each type of special character using the find & replace tool can be time-consuming and may not be wholly accurate especially when there is a large amount of data. 

In this example, we will first build a lookup table that contains all the special characters to be removed. We will then build a formula using Excel functions to remove all the special characters listed in the lookup table. The approach to building the formula is explained below. 

1. Create an array of numbers equal to the length of the text in the cell using the SEQUENCE() function
a. SEQUENCE(): this generates a matrix of numbers according
i. Syntax: SEQUENCE(rows, columns, start, step) 
1. rows: the number of rows in the matrix
2. columns: the number of columns in the matrix
3. start: (optional)the start number
4. step: (optional) the number by which each number should increment

2. Put each character in the text into the array using the MID() function
a. MID(): this returns a specified number of characters from a string
i. Syntax: MID(text, start number, number of characters)
1. text: the string from which the characters are to be extracted
2. start number: the number of the character from which the extraction is to start
3. number of characters: the number of characters to extract

3. Find the special characters using the MATCH() and remove them using the IF() function
a. MATCH(): this searches for an item in a range and returns the position of the item in the range
i. Syntax: MATCH(value, range, type)
1. value: the item to look for
2. range: the range of cells in which to search
3. type: (optional) the type of match i.e. exact or approximate

b. IF(): this evaluates a condition and returns a value depending on whether the condition is true or false
i. Syntax: IF(condition, true, false)
1. condition: the condition or statement to be evaluated
2. true: the value to be returned if the condition is true
3. false: the value to be returned if the condition is false

4. Replace the errors with actual values using the IFERROR() function
a. IFERROR(): if a formula results in an error, this function replaces the error with a specified value
i. Syntax: IFERROR(formula,value)
1. formula: the formula to be evaluated
2. value: the value to be returned if the formula returns an error

5. Collapse the array into a single string using the TEXTJOIN() function
a. TEXTJOIN() function: this combines multiple pieces of text into a single string using the specified delimiter
i. Syntax: TEXTJOIN(delimiter, ignore_empty, text1, text 2…….)
1. delimiter: the delimiter to be used between the pieces of text
2. ignore_empty: if set to TRUE, this ignores empty pieces of text
3. text1, text 2…..: the pieces of text to be joined. At least one piece is mandatory

Note: In some versions of Excel, this formula is to be treated as an array formula. After creating the entire formula, press Ctrl+Shift+Enter instead of just Enter.