Master the CHAR Function: Converting ASCII Codes to Text in Excel
=CHAR(number)The CHAR function is a powerful text manipulation tool in Excel that converts numeric ASCII codes into their corresponding characters. This intermediate-level function is essential for anyone working with text data, special characters, or legacy systems that store character information as numeric codes. Whether you're building dynamic text strings, generating special symbols, or cleaning imported data, CHAR provides a straightforward solution. Understanding CHAR opens doors to more sophisticated text processing workflows. It works in tandem with its counterpart, the CODE function, which performs the reverse operation by converting characters into ASCII codes. By mastering CHAR, you'll enhance your ability to manipulate text data programmatically, automate repetitive character insertions, and create more flexible Excel applications. This function is available across all modern Excel versions, from Excel 2007 through Excel 365, making it a reliable choice for any spreadsheet project. The CHAR function is particularly valuable when you need to insert line breaks, special formatting characters, or symbols that aren't easily accessible from your keyboard. Its simplicity belies its utility in real-world business applications where data consistency and automation are paramount.
Syntax & Parameters
The CHAR function syntax is remarkably straightforward: =CHAR(number), where 'number' is a required parameter representing an ASCII code between 1 and 255. This numeric value must be an integer; decimal values will be automatically truncated to their integer portion. The function returns the single character corresponding to that ASCII code. The 'number' parameter accepts various input types: direct numeric values (e.g., 65 for 'A'), cell references containing numbers (e.g., A1), or formulas that evaluate to numbers (e.g., CODE("A")+1). Excel's implicit conversion attempts to interpret text numbers as actual numbers, though explicit conversion using VALUE() is recommended for clarity and reliability. ASCII codes 1-31 represent control characters with special functions. For instance, CHAR(10) produces a line break within cells when used with Ctrl+Alt+Enter in older versions or with CONCATENATE in modern Excel. CHAR(9) generates a tab character useful for text alignment. Codes 32-126 represent printable characters including letters, numbers, and symbols. Codes 127-255 represent extended ASCII characters, which may display differently depending on your system's character encoding and font selection. Understanding these ranges helps you leverage CHAR effectively for specific formatting needs.
numberPractical Examples
Generating Line Breaks in Cell Content
="Hello " & A2 & CHAR(10) & "Welcome to our service!"This formula combines text strings with CHAR(10), which represents the line break character. In Excel, CHAR(10) creates a line feed that forces text to wrap to the next line within the same cell. The result displays the greeting on two lines when the cell is formatted with 'Wrap Text' enabled.
Creating Tab-Separated Data for Import
=B2 & CHAR(9) & C2 & CHAR(9) & D2CHAR(9) represents the tab character in ASCII. This formula concatenates three columns with tab delimiters between them, creating a tab-separated string suitable for import into systems expecting TSV (Tab-Separated Values) format. This approach is more reliable than manual copying and pasting.
Inserting Special Characters for Symbols
=A2 & " " & CHAR(169) & " 2024"CHAR(169) converts the ASCII code 169 into the copyright symbol ©. Extended ASCII codes 128-255 provide access to special symbols beyond standard keyboard input. This automation ensures consistent symbol insertion across hundreds of product names without manual effort or copy-paste errors.
Key Takeaways
- CHAR converts ASCII codes (1-255) into their corresponding characters; it's the inverse of the CODE function.
- CHAR(10) creates line breaks and CHAR(9) creates tabs, both essential for formatting and data export tasks.
- Extended ASCII codes (128-255) provide access to special symbols like ©, ®, and accented characters, though display consistency varies by system.
- Always validate input data and use IFERROR to handle invalid codes gracefully, preventing spreadsheet errors.
- For Unicode characters beyond ASCII, use UNICHAR function instead of CHAR in Excel 2013 and later versions.
Pro Tips
Use CHAR(10) for line breaks in formulas, but remember to enable 'Wrap Text' in cell formatting to see the line breaks display. Without this formatting, the line break character exists but remains invisible.
Impact : Ensures your multi-line formulas display correctly and improves readability of complex cell content for better data presentation.
Combine CHAR with IFERROR to handle invalid ASCII codes gracefully: =IFERROR(CHAR(A1),"Invalid code"). This prevents #VALUE! errors from disrupting your spreadsheet and provides user-friendly feedback.
Impact : Increases spreadsheet robustness and prevents cascading errors when working with user-input data or imported values that may contain invalid codes.
For extended ASCII characters (codes 128-255), test display across different computers and fonts before deploying in shared workbooks. Some characters may not render consistently due to encoding differences.
Impact : Prevents compatibility issues when sharing files and ensures your special characters display as intended for all users regardless of their system configuration.
Create a reference table in your workbook listing useful CHAR codes: 9 (tab), 10 (line break), 13 (carriage return), 169 (©), 174 (®). This eliminates the need to memorize codes and accelerates formula development.
Impact : Improves efficiency when building complex text formulas and reduces errors from incorrect ASCII code values.
Useful Combinations
CHAR + CONCATENATE for Multi-Line Formatted Text
=CONCATENATE(A2, CHAR(10), B2, CHAR(10), C2)Combines CHAR(10) line break characters with CONCATENATE to create multi-line cell content. This is particularly useful for creating formatted labels, addresses, or instructions within single cells. Requires 'Wrap Text' formatting to display properly.
CHAR + SUBSTITUTE for Character Replacement
=SUBSTITUTE(A2, CHAR(9), " ")Replaces tab characters (CHAR(9)) with spaces in imported text data. This combination is valuable when cleaning data from external sources that contain unwanted control characters or formatting that needs standardization.
CHAR + REPT for Creating Decorative Separators
="" & REPT(CHAR(151), 20)Repeats a special character (CHAR(151) creates an em-dash) 20 times to generate a decorative separator line. This combination is useful for creating visual breaks in reports, headers, or formatted output within Excel.
Common Errors
Cause: The number parameter is outside the valid range (1-255), contains text that cannot be converted to a number, or references an empty cell. For example, =CHAR(256) or =CHAR("ABC").
Solution: Validate your input data using IF statements: =IF(AND(ISNUMBER(A1),A1>=1,A1<=255),CHAR(A1),"Invalid code"). Alternatively, use IFERROR to catch conversion issues: =IFERROR(CHAR(A1),"Error").
Cause: The function name is misspelled or Excel doesn't recognize it. This occurs when typing 'CHARS' instead of 'CHAR' or when the function isn't available in your Excel version (extremely rare for CHAR).
Solution: Double-check the spelling of the function name. Ensure you're using CHAR, not CHARS or CHARACTER. Verify your Excel version supports the function—CHAR has been available since Excel 2007.
Cause: The formula references a cell that has been deleted or moved, breaking the link. For example, if your formula is =CHAR(A1) and column A is deleted, the reference becomes invalid.
Solution: Restore the deleted column using Undo (Ctrl+Z), or update the formula to reference the correct cell location. Use absolute references ($A$1) when copying formulas to prevent reference shifting issues.
Troubleshooting Checklist
- 1.Verify the ASCII code is between 1 and 255; codes outside this range will produce #VALUE! errors.
- 2.Confirm your cell is formatted with 'Wrap Text' enabled if you're using CHAR(10) for line breaks and they're not displaying.
- 3.Check that your font supports the specific character you're trying to display, especially for extended ASCII codes (128-255).
- 4.Use IFERROR or IF validation to catch invalid inputs before they propagate through dependent formulas.
- 5.Ensure you're not confusing CHAR with CODE—remember CHAR converts numbers to characters, while CODE converts characters to numbers.
- 6.Test formulas across different Excel versions and operating systems if you're using extended ASCII characters to verify consistent display.
Edge Cases
Using CHAR with ASCII codes 1-31 (control characters)
Behavior: These codes produce non-printable control characters. CHAR(10) creates a line break, CHAR(13) creates a carriage return, CHAR(9) creates a tab. Most other control characters (1-8, 11-12, 14-31) don't display visibly but may affect text processing.
Solution: Use only control characters with known effects (9, 10, 13). Avoid codes 1-8 and 14-31 unless you specifically need their control functions for data formatting or system compatibility.
Control characters are useful for data formatting but can cause unexpected behavior if used unintentionally.
CHAR with decimal numbers (e.g., 65.7)
Behavior: Excel automatically truncates decimal values to integers. CHAR(65.7) behaves identically to CHAR(65), returning 'A'. The decimal portion is discarded without rounding.
Solution: No solution needed—this behavior is intentional. If you want rounding instead of truncation, explicitly use INT() or ROUND(): =CHAR(ROUND(A1,0)).
Truncation occurs silently without errors, which can mask data quality issues if decimals are unexpected.
CHAR with extended ASCII codes displaying differently across systems
Behavior: Codes 128-255 may display differently depending on character encoding (ANSI, UTF-8), installed fonts, and operating system. A character displaying correctly on one computer might show as a different symbol or placeholder on another.
Solution: Test extended ASCII formulas across different systems before deployment. Consider using UNICHAR for Unicode characters if cross-platform consistency is critical. Document which codes are used and expected outputs.
This limitation is inherent to extended ASCII and affects data sharing and compatibility.
Limitations
- •CHAR only supports ASCII codes 1-255; it cannot generate Unicode characters beyond this range. For international characters or emoji, use UNICHAR function (Excel 2013+) instead.
- •Extended ASCII codes (128-255) display inconsistently across different systems, fonts, and character encodings, creating potential compatibility issues when sharing workbooks.
- •Control characters (codes 1-31) are non-printable and may cause unexpected behavior in data processing, making them difficult to debug when used unintentionally.
- •CHAR cannot generate multiple characters from a single call; each function invocation returns only one character. To create multi-character strings, combine multiple CHAR calls with CONCATENATE or use REPT for repetition.
Alternatives
Supports Unicode character codes beyond ASCII (1-255), enabling access to international characters, emoji, and extended symbols. Available in Excel 2013 and later versions.
When: When you need to work with non-English characters, international symbols, or modern Unicode characters that extend beyond the ASCII range.
Compatibility
✓ Excel
Since 2007
=CHAR(number) — Available in all versions from Excel 2007 through Excel 365 with identical syntax and behavior.✓Google Sheets
=CHAR(number) — Google Sheets supports CHAR with the same syntax and parameter requirements as Excel.Behaves identically to Excel; supports ASCII codes 1-255 and produces the same character outputs. Line breaks work with CHAR(10) when combined with CONCATENATE or TEXTJOIN.
✓LibreOffice
=CHAR(number) — LibreOffice Calc implements CHAR with identical syntax to Excel and Google Sheets.