The CHAR function in Excel returns the character specified by a numeric code. It is useful for inserting special characters, line breaks, or other non-printable characters into a cell. Below are 10 examples of how to use the CHAR function, including data and formulas.
1. Inserting a Line Break in a Cell
Use CHAR(10) to insert a line break in a cell (ensure "Wrap Text" is enabled).
Formula:
="Line 1" & CHAR(10) & "Line 2"
Result:
Line 1 Line 2
2. Adding a Checkmark (✓)
Use CHAR(252) or CHAR(10004) to insert a checkmark.
Formula:
=CHAR(10004)
Result:
✓
3. Inserting a Copyright Symbol (©)
Use CHAR(169) to insert a copyright symbol.
Formula:
="Copyright " & CHAR(169) & " 2023"
Result:
Copyright © 2023
4. Creating a Bulleted List
Use CHAR(149) to insert a bullet point.
Formula:
=CHAR(149) & " Item 1" & CHAR(10) & CHAR(149) & " Item 2"
Result:
• Item 1 • Item 2
5. Inserting a Degree Symbol (°)
Use CHAR(176) to insert a degree symbol.
Formula:
="Temperature: 25" & CHAR(176) & "C"
Result:
Temperature: 25°C
6. Adding a Double Quote (")
Use CHAR(34) to insert a double quote.
Formula:
="She said, " & CHAR(34) & "Hello!" & CHAR(34)
Result:
She said, "Hello!"
7. Inserting a Non-Breaking Space
Use CHAR(160) to insert a non-breaking space.
Formula:
="First" & CHAR(160) & "Last"
Result:
First Last
8. Creating a Star Rating System
Use CHAR(9733) for a filled star and CHAR(9734) for an empty star.
Formula:
=REPT(CHAR(9733), 3) & REPT(CHAR(9734), 2)
Result:
★★★☆☆
9. Inserting a Tab Character
Use CHAR(9) to insert a tab character.
Formula:
="Column1" & CHAR(9) & "Column2"
Result:
Column1 Column2
10. Combining CHAR with Other Functions
Use CHAR with CONCATENATE or TEXTJOIN to create dynamic strings.
Formula:
=TEXTJOIN(CHAR(10), TRUE, "Name: John", "Age: 30", "City: New York")
Result:
Name: John Age: 30 City: New York
CHAR Code Reference Table
| Character | CHAR Code |
|---|---|
| Line Break | 10 |
| Tab | 9 |
| Copyright Symbol | 169 |
| Degree Symbol | 176 |
| Checkmark | 10004 |
| Bullet Point | 149 |
| Double Quote | 34 |
| Non-Breaking Space | 160 |
| Filled Star | 9733 |
| Empty Star | 9734 |
These examples demonstrate the versatility of the CHAR function in Excel. You can combine it with other functions to create dynamic and visually appealing spreadsheets.
No comments:
Post a Comment