## Identify all characters in a cell value

*Article last updated on January 29, 2018*

Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell.

The same thing may happen if you try to trim blanks in a cell value and it fails to remove the character that looks like the space character.

The TRIM function removes only ANSI character 32, however, the HTML blank character number 160 is not removed by TRIM.

The following array formula demonstrated in cell C3 in the picture above will convert each character in a cell value to it's corresponding ANSI number (PC).

This will make it easier for you see all the characters that cause problems. Cell B3 contains only letters and numbers, nothing weird.

Cell B5 contains a single space character, code 32. However, cell B4 contains an HTML character that looks like a space character. Cell B7 also has a weird space character, code 143.

As you can see the formula above assists you in finding weird characters.

### Explaining formula in cell C3

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

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

becomes

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

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$6) and returns {1;2;3;4;5;6}

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("abc123", {1;2;3;4;5;6}, 1) and returns {"a";"b";"c";"1";"2";"3"}

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({"a";"b";"c";"1";"2";"3"}) and returns {97;98;99;49;50;51}.

Lastly, the TEXTJOIN function concatenates all values in the array with the delimiting character , (comma).

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

becomes

TEXTJOIN(",", TRUE,{97;98;99;49;50;51}) and returns 97,98,99,49,50,51 in cell C3.

If the cell value is empty the formula returns #VALUE, the IFERROR function then displays "EMPTY".

### ANSI table

The picture above shows characters for number 1 to 255 (ANSI).

### Download Excel *.xlsx file

Identify characters in a cell value.xlsx

### Recommended article

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

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

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

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

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

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 exists in a cell value in Excel? Answer: The […]

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 […]

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

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

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 […]

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 multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

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 […]

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form