How to remove unwanted characters in a cell

Most used functions

How to remove unwanted characters in a cell

Article updated on March 25, 2018

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in B3. If you are interested in how I built the formula, read this: How to identify characters in a cell value

For example, 160 is an HTML space character that the TRIM and CLEAN function can't remove. You can remove a single character using the SUBSTITUTE function, however, if your cell contains multiple unwanted characters the following formula will remove specific characters.

Array formula in cell B10:

=TEXTJOIN(, TRUE, IF(COUNTIF(B6:B7, CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1))), "", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)))

The characters I want to remove are in B6:B7 in ANSI code format. The formula in cell C10 shows that 160 and 143 are now gone in cell B10.

Explaining formula in cell B10

The INDEX function and the LEN function allows you to create a cell reference containing as many rows as there are characters in cell B3.

$A$1:INDEX($A$1:$A$1000, LEN(B3))

becomes

$A$1:INDEX($A$1:$A$1000, 7) and returns $A$1:$A$7.

The ROW function then creates an array from 1 to the number of characters in cell C3.

ROW($A$1:INDEX($A$1:$A$1000, LEN(B3)))

becomes

ROW($A$1:$A$7) and returns {1;2;3;4;5;6;7}

Now it is time for the MID function to split each character in cell C3 to an array.

MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)

becomes

MID(B3, {1;2;3;4;5;6;7}) and returns {" ";"5";"4";" ";"A";"";"A"}

The CODE function allows you to convert a character to ANSI code (PC). We are using an array so the function will convert all characters at the same time.

CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1))

becomes

CODE({" ";"5";"4";" ";"A";"";"A"}) and returns {160;53;52;32;65;143;65}

The COUNTIF function counts how many times 160 and 143 are found in the array.

COUNTIF(B6:B7, CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)))

becomes

COUNTIF({160;143}, {160;53;52;32;65;143;65}) and returns {1;0;0;0;0;1;0}

The IF function checks the logical expression and returns a blank value if TRUE and the charcater if FALSE. The IF function evaluates TRUE as 1 and FALSE as 0 (zero).

IF(COUNTIF(B6:B7, CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1))), "", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1))

becomes

IF({1;0;0;0;0;1;0},"",{" ";"5";"4";" ";"A";"";"A"}) and returns {"";"5";"4";" ";"A";"";"A"}.

Lastly, the TEXTJOIN function concatenates the remaining characters in the array ignoring blank values.

TEXTJOIN(, TRUE, IF(COUNTIF(B6:B7, CODE(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1))), "", MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1)))

becomes

TEXTJOIN(, TRUE, {"";"5";"4";" ";"A";"";"A"}) and returns 54 AA in cell B10.

Download Excel *.xlsx file

How to remove unwanted characters from cell value.xlsx

How to use the CLEAN function

The CLEAN function deletes nonprintable characters in a value, more specifically, it is designed to delete the first 32 nonprinting […]

Exact word in string

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Extract numbers from a value

Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in […]

How to use the CODE function

The CODE function returns a specific number for the first character of the text argument, determined by your computers character set. […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Find closest value

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Exact word in string

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Fuzzy vlookup [Array formula]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Create name initials

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

How to remove numbers from a cell value

The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Quickly concatenate values into one cell [No VBA]

Joining many many cell values in excel is not easy. The Concatenate function allows you to only reference a single […]

How to use the TEXTJOIN function

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]