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