The numbers returned by the CHAR function in Excel come from ASCII. ASCII stands for “American Standard Code for Information Interchange” and is a 7-bit character set that contains characters from 0 to 127.
The original ASCII specification encodes 128 characters into numbers ( see table below ). These include the numbers 0 to 9, lowercase a-z, uppercase A-Z, and punctuation. The first 31 characters are non-printing “control codes”, most of which are no longer used, with the exception of the carriage return (13), line feed (10), and tab (9).
ASCII codes in Excel
Excel contains two functions designed to work with ASCII codes: the CHAR function and the CODE function. To specify a character according to its ASCII number, you can use the CHAR function like this:
=CHAR(65) // returns "A"
=CHAR(13) // carriage return
To return the ASCII number for a character, you can use the CODE function :
=CODE("A") // returns 65
Note: The Unicode character set is widely used these days on the web and in modern applications. Unlike ASCII, Unicode is a standard designed to support all of the world’s languages, and has many thousands of characters. Excel has two functions designed to work with this extended character set: UNICHAR and UNICODE .
ASCII codes
The table below shows standard printable ASCII characters and codes. The second table shows non-printable control codes .
| Character | Number | Notes |
|---|---|---|
| 32 | space character | |
| ! | 33 | exclamation mark |
| " | 34 | quotation mark |
| # | 35 | number sign |
| $ | 36 | dollar sign |
| % | 37 | percent sign |
| & | 38 | ampersand |
| ' | 39 | apostrophe |
| ( | 40 | left parenthesis |
| ) | 41 | right parenthesis |
| * | 42 | asterisk |
| + | 43 | plus sign |
| , | 44 | comma |
| - | 45 | hyphen |
| . | 46 | period |
| / | 47 | slash |
| 0 | 48 | digit 0 |
| 1 | 49 | digit 1 |
| 2 | 50 | digit 2 |
| 3 | 51 | digit 3 |
| 4 | 52 | digit 4 |
| 5 | 53 | digit 5 |
| 6 | 54 | digit 6 |
| 7 | 55 | digit 7 |
| 8 | 56 | digit 8 |
| 9 | 57 | digit 9 |
| : | 58 | colon |
| ; | 59 | semicolon |
| < | 60 | less-than |
| = | 61 | equals-to |
| > | 62 | greater-than |
| ? | 63 | question mark |
| @ | 64 | at sign |
| A | 65 | uppercase A |
| B | 66 | uppercase B |
| C | 67 | uppercase C |
| D | 68 | uppercase D |
| E | 69 | uppercase E |
| F | 70 | uppercase F |
| G | 71 | uppercase G |
| H | 72 | uppercase H |
| I | 73 | uppercase I |
| J | 74 | uppercase J |
| K | 75 | uppercase K |
| L | 76 | uppercase L |
| M | 77 | uppercase M |
| N | 78 | uppercase N |
| O | 79 | uppercase O |
| P | 80 | uppercase P |
| Q | 81 | uppercase Q |
| R | 82 | uppercase R |
| S | 83 | uppercase S |
| T | 84 | uppercase T |
| U | 85 | uppercase U |
| V | 86 | uppercase V |
| W | 87 | uppercase W |
| X | 88 | uppercase X |
| Y | 89 | uppercase Y |
| Z | 90 | uppercase Z |
| [ | 91 | left square bracket |
| \ | 92 | backslash |
| ] | 93 | right square bracket |
| ^ | 94 | caret |
| _ | 95 | underscore |
| ` | 96 | grave accent |
| a | 97 | lowercase a |
| b | 98 | lowercase b |
| c | 99 | lowercase c |
| d | 100 | lowercase d |
| e | 101 | lowercase e |
| f | 102 | lowercase f |
| g | 103 | lowercase g |
| h | 104 | lowercase h |
| i | 105 | lowercase i |
| j | 106 | lowercase j |
| k | 107 | lowercase k |
| l | 108 | lowercase l |
| m | 109 | lowercase m |
| n | 110 | lowercase n |
| o | 111 | lowercase o |
| p | 112 | lowercase p |
| q | 113 | lowercase q |
| r | 114 | lowercase r |
| s | 115 | lowercase s |
| t | 116 | lowercase t |
| u | 117 | lowercase u |
| v | 118 | lowercase v |
| w | 119 | lowercase w |
| x | 120 | lowercase x |
| y | 121 | lowercase y |
| z | 122 | lowercase z |
| { | 123 | left curly brace |
| | | 124 | vertical bar |
| } | 125 | right curly brace |
| ~ | 126 | tilde |
ASCII control codes
The table below shows the first 31 characters in ASCII, which are non-printing control codes. These codes were originally intended to control devices, and are no longer used, with the exception of the carriage return (13), line feed (10), and tab (9).
| Character | Number | Notes |
|---|---|---|
| NUL | 0 | null character |
| SOH | 1 | start of header |
| STX | 2 | start of text |
| ETX | 3 | end of text |
| EOT | 4 | end of transmission |
| ENQ | 5 | enquiry |
| ACK | 6 | acknowledge |
| BEL | 7 | bell (ring) |
| BS | 8 | backspace |
| HT | 9 | horizontal tab |
| LF | 10 | line feed |
| VT | 11 | vertical tab |
| FF | 12 | form feed |
| CR | 13 | carriage return |
| SO | 14 | shift out |
| SI | 15 | shift in |
| DLE | 16 | data link escape |
| DC1 | 17 | device control 1 |
| DC2 | 18 | device control 2 |
| DC3 | 19 | device control 3 |
| DC4 | 20 | device control 4 |
| NAK | 21 | negative acknowledge |
| SYN | 22 | synchronize |
| ETB | 23 | end transmission block |
| CAN | 24 | cancel |
| EM | 25 | end of medium |
| SUB | 26 | substitute |
| ESC | 27 | escape |
| FS | 28 | file separator |
| GS | 29 | group separator |
| RS | 30 | record separator |
| US | 31 | unit separator |
| DEL | 127 | delete control character |
The term “BigNum”, which stands for “Big Number”, is used to represent the largest allowed positive number in Excel, which is 9.99999999999999E+307
BigNum is used in certain lookup formulas constructed in a way to find the largest value that is less than or equal to a search value. Because BigNum is an improbably large value, the lookup will find the previous numeric value.
For example, you can use the following formula to find the position of the last numeric value in a column:
=MATCH(9.99999999999999E+307,range)
In practice, any improbably large value will do, so you could trim the above to:
=MATCH(9.99E+307,range)
The reason this works has to do with the default behavior of MATCH, which uses a binary search algorithm to find the largest value that is less than or equal to the search value in a range. In this (default) mode, MATCH assumes values are sorted in descending order. When BigNum is, by design, not found, MATCH returns the position of the last number in the range.
For more details, see: Last row in numeric data.
BigNum with other lookup functions
The BigNum approach can be used with other functions that support binary search, for example:
=MATCH(bignum,range,1)
=VLOOKUP(bignum,range,1,1)
=HLOOKUP(bignum,range,1,1)
=LOOKUP(bignum,range)
Note match type is set to 1 in the first three examples to explicitly set binary search, often referred to as approximate match.
