Thread: 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.

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.

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.)

=CHAR(MOD(FLOOR((ROW()-0.01)/676,1),26)+65)&CHAR(MOD(FLOOR((ROW()-0.01)/26,1),26)+65)&CHAR(MOD(ROW()-1,26)+65)

3. What is your goal in doing this?

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.

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.