## 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:

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

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 […]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 […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article