ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #360 - Revisiting typing special characters

Author: David Lyford-Smith

Published: 22 Sep 2020

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

What is a special character?

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.

Insert special character

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

Special characters

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.

Via functions

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:

Functions

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:

Unicode

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:

Unicode

Typing

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.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250