Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Increment Letters (A+1 = B)

  1. #11
    Board Regular sadi's Avatar
    Join Date
    Jun 2009
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    boim Thanks

    Itís working & sorry for "Than"

    Thanks Again

  2. #12
    New Member
    Join Date
    Sep 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Increment Letters (A+1 = B)

    hi,

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

    any help? thanks

  3. #13
    New Member
    Join Date
    May 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: Increment Letters (A+1 = B)

    Take a look at this answer - it helped me with a similar question:
    http://answers.yahoo.com/question/in...3071401AAGKY73

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

    -- removed inline image ---

  4. #14
    New Member
    Join Date
    May 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    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

  5. #15
    New Member
    Join Date
    Sep 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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??


    Quote Originally Posted by boim;22[CODE
    [/CODE]12232]You meant "then" not "than" ?

    one possible way is:

    Code:
    =SUBSTITUTE(ADDRESS(1,COLUMN($A$1)+ B2,4),"1","")
    where B2 is the amount of increment.

    or put this from A1 down

    Code:
    =SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")
    Have you considered tusharm / Juan PG suggestions instead of trying whatever approach you're doing now?

  6. #16
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    HI

    Try in any cell:-
    Code:
    =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
    Last edited by ukmikeb; Oct 4th, 2015 at 06:37 AM. Reason: addn
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  7. #17
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    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 :-
    Code:
    =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)
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  8. #18
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,887
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    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

  9. #19
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,798
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    Quote Originally Posted by migooz View Post
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #20
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Increment Letters (A+1 = B)

    Or try this:

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •