Increment Letters (A+1 = B)

[Guest]

How do i increment the letters?
I want to be able to select cells A1, then B2, then C3 etc.
I can easily increment the rows, how do i increment columns?? thks

 

[sadi]

boim Thanks
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
It’s working & sorry for "Than"<o></o>
<o></o>
Thanks Again<o></o>

 

[alxyzrian]

hi,

help please. im trying to create an increment from AAA001 to ZZZ999 (AAA001,AAA002,AAA003...AAA999, AAB001, AAB002, AAB999)

any help? thanks

 

[e-mouse]

Take a look at this answer - it helped me with a similar question:
http://answers.yahoo.com/question/index?qid=20071003071401AAGKY73

By the way, I needed to increment the letters, whenever a new item came up (D column = "New").

-- removed inline image ---

 

[e-mouse]

Sorry, the pictures didn't show. So these are the table parts that I was talking of:

1st part:


-- removed inline image ---
D E
1 New A
2 Dupl A
3 New B
4 Dupl B
5 New C

2nd part:

D E
56 Dupl Z
57 New AA
58 Dupl AA
59 New AB
60 Dupl AB

 

[migooz]

FROM A-Z and THEN ZA -ZZ THEN ZZA - ZZZ is this possible??

is there a way to:

FROM A-Z and THEN ZA -ZZ THEN ZZA - ZZZ, etc is this possible??

[/CODE]12232]You meant "then" not "than" ?

one possible way is:

=SUBSTITUTE(ADDRESS(1,COLUMN($A$1)+ B2,4),"1","")

where B2 is the amount of increment.

or put this from A1 down

=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

Have you considered tusharm / Juan PG suggestions instead of trying whatever approach you're doing now?

 

[ukmikeb]

HI

Try in any cell:-

=LEFT(ADDRESS(1,ROWS($1:1),2),FIND("$",ADDRESS(1,ROWS($1:1),2))-1)

and drag down for A-Z.

Adjust each ROWS part by adding 666 to get ZA onwards by starting the formula from row 27.

ZZA onwards is not possible with this formula.

hth

 

[ukmikeb]

Apologies I mucked up on the calculations for ZA in the above post.

Here is the corrected formula which will do A-Z and ZA - ZZ :-

=LEFT(ADDRESS(1,ROWS($1:1)+(ROWS($1:1)>26)*650,2),FIND("$",ADDRESS(1,ROWS($1:1)+(ROWS($1:1)>26)*650,2))-1)

 

[bosco_yip]

Try this formula and drag down :

=LEFT(ADDRESS(1,ROWS($1:1),4),FIND(1,ADDRESS(1,ROWS($1:1),4))-1)

In Excel 2003, the above formula can obtain A - Z, AA - IV (totally 256 nos.)

In Excel 2007, the above formula can obtain A - Z, AA - ZZ and AAA - XFD ( totally 16384 nos.)

Regards

 

[Rick Rothstein]

is there a way to:

FROM A-Z and THEN ZA -ZZ THEN ZZA - ZZZ, etc is this possible??

I believe this formula does what you are asking for...

=REPT("Z",INT(ROWS($1:1)-1)/26)&CHAR(MOD(ROWS($1:1)-1,26)+65)

 

[István Hirsch]

Or try this:

=SUBSTITUTE(ADDRESS(1,ROW(),4),1,"")