+ Reply to Thread
Results 1 to 6 of 6

Need a formula to create an alphabetical code

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need a formula to create an alphabetical code

    Hi, as the title says I am new to excel and need some help.

    I would like excel to creat an alphabetical code. I am looking for the formula to create every combination possible in a format like:

    AAA-AAA

    It can be in one cell or two cells if the "-"can not be incorporated but must be progressive "AAA-AAB...AAA-AAC...AAA-AAD" and so on.

    Any help would be great.

    Thanks.

    P.S. Hope this is in the right place.
    Last edited by Acer2005; 11-17-2010 at 07:51 PM. Reason: Wrong title

  2. #2
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Need a formula to create an alphabetical code

    I don't think this will be that easy, if not impossible, or at least with my level of skills.

    But maybe one of the code gurus can take the attached make it go in order. You can use the function CHAR, and with values from 65-90, you'll get characters from A-Z (in capital), small is from 91-something...

    So, as I was saying maybe with some code we can make cells D1:J1, go in order of
    65,65,65-65,65,66
    Then by re-running the code or updating the sheet
    65,65,65-65,65,67
    65,65,65-65,65,68
    65,65,65-65,65,69
    65,65,65-65,65,70
    ....
    90,90,90-90,90,90
    And then reset and start all over.
    Attached Files Attached Files
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: Need a formula to create an alphabetical code

    A few things to note:

    1. AAA-AAA through ZZZ-ZZZ is 26^6 (308,915,776) letter combinations. Even using Excel 2007/2010 that would take up 309 columns x 1 million rows to generate all of them.

    2. To do, say, just the last three characters down one column would take 17,576 cells (26^3). The following formula should do that. (To add another character to the left would result in 456,976 rows (26^4), which could be done in the latest versions.)
    Please Login or Register  to view this content.
    3. What is your goal in doing this?

  4. #4
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Need a formula to create an alphabetical code

    Well Paul, remember that the OP wanted it in one cell or two, I gave it to him in 6 cells and those change randomly by pressing F9 function key. But they are all over the place not in other as he wanted them.

    tha's why I suggested code that changes those cells only in order.

  5. #5
    Registered User
    Join Date
    11-17-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Need a formula to create an alphabetical code

    Hi, Thanks for the help but I don't need it any more...Sorry for the trouble.

    My son wanted it for some reason. He has a a keen interest in consoles and PC's so don't really know what it was for...lol.

    If I ask him any thing it just goes right over my head.

    Thanks again...

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: Need a formula to create an alphabetical code

    While it doesn't pertain to Excel, it may be worth your while to find out what he was trying to do. Nothing, not even iTunes, can crash a PC quicker than a mischievous youngster with too much free time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1