How to generate a string of random letters in Excel
The CHAR function returns a character that corresponds to the number in the character set used by your computer (ANSI for Windows). There are up to 255 different characters in the set, with the capital letters from A-Z starting at number 65. Z is 90. To generate a random letter you can use the RANDBETWEEN function:
=CHAR(RANDBETWEEN(65,90))
If you want to create a string of several random letters, simply repeat the formula with an ampersand (&) between each one. You can also add static elements such as a hyphen.
How to find the CHAR number of a specific letter
The ANSI table might look a little different depending on the settings of your computer, but you can easily make a table that shows all the characters available to you. Type the formula =CHAR(ROW(A1)) in cell A1, and copy it down to A255. This will give you a list of all the characters the CHAR function can return on your computer.
If you are looking for the number of one specific character, e.g. “Æ”, you can use the CODE function:
=CODE(“Æ”)
This function returns 198, which is the ANSI number of the character Æ.
How to add a line break using the CHAR function
The easiest way to force a line break inside a cell in Excel is to press Alt+Enter, but that only works if you actually type the text into the cell. Sometimes you want to create a text using elements from other cells, and that’s when the CHAR function comes in handy again. The ANSI code for a hard line break is 10, so if you want to concatenate the text in A1 with the text in B1 with a line break between them, you can use this formula:
=A1&CHAR(10)&B1
You have to allow for line breaks by clicking on “Wrap text” on the Home tab.
More on text functions in Excel:
- Join cells in Excel and remove excess commas between elements
- Use Excel to Count Words
- How to use Excel to Count Characters in a Text String
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.
Great info. I really like using CHAR(10) to create a line break.