Special Characters In Excel
|Using a typical computer
keyboard, you can directly type in approximately 94 different characters,
such as letters and number other symbols like punctuation marks.
However, there are many other symbols available. In a typical font
such as Arial, there are some 200 different symbols available, including
the British Pound sign £, the Euro Currency symbol €, and the copyright
mark ©. These symbols cannot be directly entered with a single key
on a typical (US) keyboard. This page describes how to work
with these special characters in Excel.
All characters are assigned a standard code number. This is how characters are stored internally by the computer. For example, the capital letter "A" is stored as the number 65. In order to use special symbols like the symbol £, you usually need to know the code number. However, I have written a free Add-In for Excel that will display a list of symbols. This is described later.
Entering Symbols With The Keyboard
If you know the code number, you can enter that character any time by holding down the ALT key, and typing the 4-digit code number on the numeric keypad (the keys on the far right of your keyboard -- do not use the "normal" number keys above the letter keys). Even though character code numbers are only three digits (between 0 and 255), you must type in the leading zeros to ensure that you get the proper character. For example, to enter in the British Pound sign, whose code is 163, hold down the ALT key, and type 0163 on the numeric keypad.
Common Symbols And Codes
Creating A List Of Symbols
Of course, unless you use symbol frequently, you probably don't know what the code number is. You can quickly create a simple list of symbols on an Excel worksheet by entering the following formula in cell A1, and then copying the formula down through row 255:
This will display in each cell the character whose code number is that cell's row number. For example, the Euro Currency symbol will appear in cell A128, which indicates that its code number is 128.
You can change the font of the column to see what characters are supplied by various fonts. Fonts with lots of useful symbols include Symbol, WebDings, the 3 WingDings fonts, Ref Specialty, ENCICON, Marlett, MonoType Sorts, and MS Reference. Note that you may not have all these fonts on your computer.
Using Symbols In Formulas
If you need to use a special symbol in a worksheet formula, you can use the function to return the character from its code number. For example, suppose you want to add a copyright notice to your worksheet. You could use the following formula:
=CHAR(169) & " Copyright, 2000."
The CHAR(169) function returns the actual copyright symbol, so it will appear properly in the cell. The "inverse" of the CHAR function is the CODE function. While CHAR takes a number and returns the actual character, the CODE function takes a character and returns the code number. For example, the following formula returns 65, the character code of a capital letter "A".
The VBA programming equivalents of the CHAR and CODE functions are Chr() and Asc(), respectively. For example, to display a message box with a copyright message, use the following code:
MsgBox Chr(169) & " Copyright, 2000"
The procedures described above assume that you know what character code to use. Unlike Microsoft Word, Excel does not have an "Insert Symbol" function. The Symbolizer add-in is a simple remedy to this oversight. When you install the Symbolizer program, it will add an item to your Tools menu called Symbolizer. Choosing this menu item will display the Symbolizer screen, shown below.
The main list box displays all the symbols that are
available in the selected font. You can scroll through the list
box to find the symbol you need. Once you've clicked on it, you can
copy it to the Windows clipboard by clicking the Copy button, or transfer it
directly to the active cell. The Put button will replace the text of
the active cell with the selected character, and Append will put the
selected character at the end of the existing text.
By default, Symbolizer will display all 255 characters for a font. However, you can click the Quick button to reduce the list to 12 of the most commonly used symbols, including various currency symbols, and copyright and trademark symbols.
|The Symbolizer program is provided as an xls workbook rather than as an xla add-in. To make it into an add-in, open the xls workbook, and do a Save As, and change the "Save As Type" value to "Microsoft Excel Add-In". Then close all the open workbooks in Excel, and go to the Tools menu, and choose the Add-Ins menu item. In the dialog box that appears, click the Browse button, and navigate to the saved file. This will add it to the list of available add-ins. Scroll down to "Symbolizer" and ensure that the box is checked. Click here to download a zip file containing Symbolize.xls. This workbook will work only with Excel97 and Excel2000. It will not work with Excel95 or earlier versions.