BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

MS Excel Formulas: CHAR/CODE - Text Functions

CHAR function: Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. (Source: Microsoft Office Help)

For example: CHAR(65) would return the alphabet A, similarly CHAR(10) would return new-line.

Some important ASCII codes helpful in MS Excel:
9     -      Horizontal Tab
10   -     New Line character
11   -    Vertical Tab
32   -    Space
33 to 41 - Special Characters above numbers on keyboard i.e. !, @, # .... to ... *, ( and ).
48 to 57 - Numbers 0 to 9
65 to 90 - Capital Letters A to Z
97 to 122 - Small Letters a to z
153   -   Trademark symbol TM 
169   -   Copyright Symbol ©

Currency Symbols:
¤ - Generic Currency comes from CHAR(164)
$ - Dollar comes from CHAR(36)
£ - Pound comes from CHAR(163)
¥ -  Yen comes from CHAR(165)
€  - Euro symbol comes from CHAR(128)
¢  - Cent symbol comes from CHAR(162)

Please note that 
i) CHR function in VBA is equivalent to CHAR function in MS Excel. The behavious of CHR function in VBA is analogous to CHAR function in MS Excel.
ii) CODE function in MS Excel is inverse of CHAR function. For eg. CODE("A") would return 65. It means CODE(CHAR(100)) = 100.

Let's move on to some important examples to illustrate the extensive use of CHAR function in MS Excel:
Example 01: If you would like to merge information from multiple cells into one cell including 'new line', you could use CHAR(10) in the below manner using either of the CONCATENATE function or '&' operator.

    i) Carefully notice the formulas in F column.

    ii) See the result, how cleanly and beautifully the information is consolidated and displayed in MS Excel.

Example 02: Similarly format a single line data to multiple lines:

Example 03: To introduce special characters or symbols in a text strings, try the MS Excel CHAR function:
 ="Historical data of Pound (" & CHAR(163) & "), Euro (" & CHAR(128) & ") and Dollar (" & (CHAR(36) & ")")

This would return the following text string:

"Historical data of Pound (£), Euro(€) and Dollar ($)"
The information below is purely for reference purposes only.

What is ASCII?
ASCII stands for American Standard Code for Information Interchange.

Where it is originally used?
Unlike you and me these computers since their birth can only understand numbers, hence an ASCII code is the numerical representation of a character such as 'z' or '$'. ASCII was developed a long time ago and now the non-printing characters are rarely used for their original purpose. Below is the ASCII character table and this includes descriptions of the first 32 non-printing characters. ASCII was actually designed for use with teletypes and so the descriptions are somewhat obscure.

What does it mean today?
If someone says they want your RESUME, however in ASCII format then what they mean is that they want 'plain' text with no formatting such as tabs, bold or underscoring - the raw format that any computer can understand. This is usually so they can easily import the file into their own applications without issues.

The complete list of ASCII codes: