30 Excel Functions in 30 Days: 07 – CODE

Icon30DayCongratulations! You’ve made it to the first weekend in the 30XL30D challenge, including yesterday’s investigation of the FIXED function. We’ll take it easy today, and look at a function that doesn’t have too many examples — the CODE function. It can work with other functions, in long, complicated formulas, but today we’ll focus on what it can do on its own, and in simple formulas.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the CODE information and examples, and if you have other tips or examples, please share them in the comments. Wear your secret deCODEr ring, if you have one.

Function 07: CODE

The CODE function returns a numeric code for the first character in a text string. For Windows, the returned code is from the ANSI character set, and for Macintosh, the code is from the Macintosh character set.

Code00

How Could You Use CODE?

The CODE function can help unravel mysteries in your data, such as:

  • What hidden character is at the end of imported text?
  • How can I type a special symbol in a cell?

CODE Syntax

The CODE function has the following syntax:

  • CODE(text)
    • text is the text string from which you want the first character’s code

CODE Traps

Results could be different if you switch to a different operating system. The codes for the ASCII character set (codes 32 to 126) are consistent, and most can be found on your keyboard. However, the characters for the higher numbers (129 to 254) may vary, as you can see in the comparison charts shown here:

Differences between ANSI, ISO-8859-1 and MacRoman character sets

For example, the ANSI code 189 is ½ and for the Macintosh it is O

Example 1: Get Hidden Character’s Code

When you copy text from a website, it might include hidden characters. The CODE function can be used to identify what those hidden characters are. For example, there is a text string in cell B3, and only “test” is visible — 4 characters. In cell C3, the LEN function shows that there are 5 characters in cell B3.

To identify the last character’s code, you can use the RIGHT function, to return the last character. Then, use the CODE function to return the code for that character.

=CODE(RIGHT(B3,1))

Code01

In cell D3, the RIGHT/CODE formula shows that the last character has the code 160, which is a non-breaking space used on websites.

Example 2: Find a Symbol’s Code

To insert special characters in an Excel worksheet, you can use the Symbol command on the Ribbon’s Insert tab. For example, you can insert a degree symbol ° or a copyright symbol ©.

After you insert a symbol, you can determine its code, by using the CODE function

=IF(C3=””,””,CODE(RIGHT(C3,1)))

Code02

Once you know the code, you can use the numeric keypad (not the regular numbers) to insert the symbol. The code for the copyright symbol is 169. Follow these steps to enter that symbol in a cell.

On a keyboard with a numeric keypad

  1. On the keyboard, press the Alt key
  2. On the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
  3. Press Enter, to see the copyright symbol in the cell.

On a keyboard with no numeric keypad

On your laptop, you might need to press special keys to use the numeric keypad function. Check the owner’s manual, for directions. Here’s what works on my Dell laptop.

  1. Press the Fn key, and the F4 key, to enable NumLock
  2. Locate the numeric keypad within the letters on the keyboard. On my keyboard, J=1, K=2, etc.
  3. Press the Alt key, and the Fn key, and using the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
  4. Press Enter, to see the copyright symbol in the cell.
  5. When finished, press the Fn key, and the F4 key, to disable NumLock

Download the CODE Function File

To see the formulas used in today’s examples, you can download the CODE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the CODE Video

To see a demonstration of the examples in the CODE function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Get Character Number With Excel CODE Function

 

_____________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *