A special character is anything outside of what you can directly enter using your keyboard. Examples might include currency symbols, non-English characters or accented letters, mathematical symbols, and even symbols such as chess pieces, arrows, and emoji. There’s really a huge array available!

There are a few ways of accessing these –from your keyboard, from Excel’s menus, or via functions. We’ll look at each in turn.

There’s a menu option for special characters under Insert => Symbols:

This lists out a large selection of symbols from various languages and character sets, and also keeps a track of your recently used symbols, which is excellent for commonly used ones. Also notice that a “character code” for each is displayed in the bottom right – more on that later.

There are four functions relevant here – two simple and two deep, of which one of each is for identifying characters and one is for producing them. First, the simple ones:

                =CHAR(number)
                =CODE(cell or character)

CHAR can return a character from a special list of commonly-used ones, based on a numeric listing.  CHAR uses your device’s character set (the 218-character ANSI set for Windows machines, and the Macintosh character set for Apple devices). The list is fairly limited – capital and normal letters, numbers, and the most common punctuation marks and symbols.

To identify which number a particular character is, you can type or copy it from somewhere else, and then use the CODE function to identify it.

Realistically, these functions are quite limited – the only thing I use them for is that I memorised that CHAR(10) is the line-break character, which is useful for making multi-line text:

Note that this only works if you have Wrap Text enabled!

The more complete functions, available from Excel 2013 onwards, are:

                =UNICHAR(number)
                =UNICODE(cell or character)

Essentially these work the exact same way, except instead of using the 218-character ANSI character set, they use Unicode – an international standard set of over 140,000 characters that is intended to include every symbol used in human communication. This allows you to access and type almost anything.

Most sources list Unicode addresses in hexadecimal – base 16 – so you may need to convert the number you want to decimal before Excel will use it. The HEX2DEC function does this. Let’s take as our example the € character, which in the Symbols menu above we can see has Unicode character code 20AC:

Because Unicode is a standardised thing, Unicode characters can be rendered anywhere in Excel – so you can even use them in things such as dropdown menus:

Finally, you can type any of these functions directly using the following method:

1. Press and hold the Alt key
2. Type out the number of the character you wish to type on a numberpad
3. Release the Alt key

The number will be the same Unicode number as before – so for example you can hold Alt while typing 8364 (the decimal representation of 20AC) to type a €.  Remember or print out a few of your most commonly used numbers and you can access them quickly and easily!  I particularly like the playing card symbols as quick examples, as they are assigned to 3, 4, 5, and 6: ♥♦♣♠.

And that’s it!  Explore the huge world of Unicode, emojis, and symbols and find what you need for your workbooks.

• Previous post –  #359 Audit sampling templates: VBA
• TOTW index
• Next post – How to do data entry