Cellwise Start free

Text function

Excel CODE function

CODE returns the number of the first character in a piece of text: 65 for A, 97 for a, 32 for a space. Its real job is detective work. When two cells look identical but a lookup says they are different, CODE on the last character shows what is actually there.

=CODE(text)

Arguments

text
The text whose first character you want the code of. Empty text returns #VALUE!.

CODE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

AB
1Customer (pasted)Customer (typed)
2Acme Ltd Acme Ltd
3Bluefin CoBluefin Co
4 Harbor IncHarbor Inc

Download this sheet as an Excel practice file (.xlsx)

  1. =CODE(RIGHT(A2,1))

    Result: 160

    The last character of the pasted name is code 160, a non-breaking space from a web page or PDF. It looks like a space, but TRIM does not remove it.

  2. =CODE(RIGHT(B2,1))

    Result: 100

    The typed name ends in d, code 100. So the two cells really are different, which is why the lookup failed.

  3. =CODE(A4)

    Result: 160

    The problem can be at the start too: the first character of A4 is also code 160.

  4. =CODE("a")-CODE("A")

    Result: 32

    Lowercase letters are 32 codes after uppercase ones (a is 97, A is 65). That is why FIND and EXACT treat them as different.

Reference: Non-breaking space

How to use CODE

  1. Pick the cell that will not match.
  2. Type =CODE(RIGHT(A2,1)) to test the last character, or =CODE(A2) for the first.
  3. Look up the number: 32 is a normal space, 160 a non-breaking space, 10 a line break, 9 a tab.
  4. Remove it: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) for 160, CLEAN for codes below 32.

Common CODE mistakes

Expecting CODE to read the whole cell

CODE only looks at the first character. Use RIGHT(A2,1) or MID(A2,n,1) to test another position.

Trusting TRIM to fix code 160

TRIM only removes the normal space, code 32. Swap 160 for a normal space with SUBSTITUTE first, then TRIM.

Using it on symbols outside the basic set

For emoji and most symbols, CODE does not return the real code. Use UNICODE (Excel 2013 and later), which returns the full Unicode number.

CODE FAQ

How do I find hidden characters in Excel?

Compare =LEN(A2) with what you see, then test the ends with =CODE(LEFT(A2,1)) and =CODE(RIGHT(A2,1)). 160 is a non-breaking space, 10 a line break and 9 a tab.

What is CHAR(160) in Excel?

A non-breaking space, common in data copied from web pages. It looks like a space but is a different character, so TRIM and lookups treat it differently.

What is the opposite of CODE in Excel?

CHAR. =CHAR(65) returns A and =CODE("A") returns 65.

Related functions

Updated