Here, we will learn how to use the Excel text functions: CHAR and CODE. Later, we will also learn how to generate capital and small letters using CHAR with the SEQUENCE function, that generates a list of sequential numbers.
Excel CHAR function
The Excel CHAR function returns the character when given the valid character numeric code. For example, =CHAR(65) will return the capital letter "A". Similarly, for the small letter "a", the numeric code is 97. It always returns the result value in the text format. The formula in B4, copied down, is:
=CHAR(A4)
Excel CODE function
The Excel CODE function returns the character numeric code for the given character. For example, =CODE("A") returns the numeric code 65. Similarly, for the numeric code 97, the character is small letter "a". It always returns the result value in the text format. The formula in E4, copied down, is:
=CODE(D4)
Generating capital and small letters using CHAR with SEQUENCE
To generate capital and small letters, use the CHAR formula and nest the SEQUENCE function in the number argument. My goal is to generate capital letters in column G and small letters in column H. The formula in G4, copied down, is:
=CHAR(SEQUENCE(26,,65))
The formula in H4, copied down, is:
=CHAR(SEQUENCE(26,,97))
As soon as I press Enter in both the cases, the formulas spill the results into the multiple cells automatically in the ranges G4:G29 and H4:H29 respectively.
How these formulas work
Let's look at the first formula. SEQUENCE returns an array of sequential numbers 65-90, 26 rows by 1 column, starting at 65. The number 65 goes to the number argument of CHAR and thus, CHAR returns the capital letter "A". The number 65 is incremented by 1 for each row and so, CHAR returns the capital letter "B" for 66 in the next row, "C" for 67 in the third row, and so on.
Let's look at the second formula now. SEQUENCE returns an array of sequential numbers 97-122, 26 rows by 1 column, starting at 97. The number 97 goes to the number argument of CHAR and thus, CHAR returns the small letter "a". The number 97 is incremented by 1 for each row and so, CHAR returns the small letter "b" for 98 in the next row, "c" for 99 in the third row, and so on.
You have successfully learnt how to use the Excel text functions: CHAR and CODE. You have also learnt how to generate capital and small letters using CHAR with SEQUENCE! I hope this post helped you.
