Excel is fun to play with.
How to increment letters in Excel like we increment the number - by dragging a cell with a letter to fill the rest?
There is a way of getting Excel to fill a series of letters.
The key to do it is using two functions:
1) CHAR(number) Number is a number between 1 and 255.
2) CODE(text) Text is the text whose first character is coded.
(The corresponding Codes for letters from A to Z are 65 to 90. The corresponding Codes for letters from a to z are 97 to 122.)
For Example: Formula = CODE("A") Result=65
Formula = Char(65) Result=A
Then, let’s fill in a series of letters M to X in the excel spreadsheet.
Step1: In cell A2, we type in M
Step2: In Cell A3, We type in =CHAR(CODE(A2)+1)
Step3: We select cell A3, drag down to auto fill to cell A13.
Or Copy A3, Paste down to A13.
Next task is how we fill in the letter series like AA, AB ....AZ, BA, BB….ZZ.
Besides using the formula CODE( ) and CHAR( ), we need the help of IF( ), LEFT( ) and RIGHT( ) function.
Then, let’s fill in a series of letters AA to ZZ in a new Excel sheet.
Step 1: In cell A2, we type in AA
Step 2: In Cell A3, We type in =IF(RIGHT(A2,1)<>"Z",LEFT(A2,1)&CHAR(CODE(RIGHT(A2,1))+1),CHAR(CODE(A2)+1)&"A")
Step 3: We select cell A3, drag down to auto fill to cell A677
Can you think of the way to fill in series of letters: AAA, AAB, AAC…ABA, ABC,….AZZ, BAA,…..ZZZ?
© Copyright Exceltipsandkeys All Rights Reserved.