Congratulations! You’ve made it to the first weekend in the 30XL30D challenge, including yesterday’s investigation of the FIXED function.
CODE 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.
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.
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))
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)))
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
- On the keyboard, press the Alt key
- On the numeric keypad, type the code as a 4-digit number (add leading zeros if necessary): 0169
- 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.
- Press the Fn key, and the F4 key, to enable NumLock
- Locate the numeric keypad within the letters on the keyboard. On my keyboard, J=1, K=2, etc.
- 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
- Press Enter, to see the copyright symbol in the cell.
- 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.
_____________
One thought on “30 Excel Functions in 30 Days: 07 – CODE”