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.
No comments:
Post a Comment