# Sequential Alphanumeric entries

1. ## Sequential Alphanumeric entries

Hi, I'm new to the forum, as I'm also quite new to Excel and although variations of this topic have been covered in several other threads, none of them seem to answer my query - unless I just can't find it..

I'm trying to create a single column of stock bin codes for eventual conversion to barcodes. The bin codes run like this:
A1A
A1B
A1C - etc. to A1H, then the sequence starts again with:
A2A
A2B
A2C etc. up to A6H, when the 1st character changes to B and the sequence starts again

This needs to continue until the sequence reaches Z6H

Can anyone please advise me in simple terms how to do this without having to enter each code manually?

2. ## Re: Sequential Alphanumeric entries

One way, with VBA:

``Please Login or Register  to view this content.``

Regards, TMS

3. ## Re: Sequential Alphanumeric entries

The following code will generate the codes in column A starting at row 10
``Please Login or Register  to view this content.``

4. ## Re: Sequential Alphanumeric entries

Alternative, formula-based solution:

="A"&1+INT((ROWS(\$1:1)-1)/8)&CHAR(65+MOD(ROWS(\$1:1)-1,8))

Copy down as required.

Regards

5. ## Re: Sequential Alphanumeric entries

Obviously XOR LX's solution is a lot shorter but seeing as I spent a while working it out:
=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",INT(ROW(A1)/48)+1,1) & IF(RIGHT(A1,1)="H",MAX(1,MOD(--MID(A1,2,1)+1,7)),MID(A1,2,1)) & MID("HABCDEFG",MOD(FIND(RIGHT(A1,1),"ABCDEFGH")+1,8)+1,1)

in A2 with "A1A" type in A1 and copy down

6. ## Re: Sequential Alphanumeric entries

Well I worked on this, I might as well posti it

from row 1
=CHAR(97+INT(MOD((ROW()-1)/48,48)))&CHAR(49+INT(MOD((ROW()-1)/8,6)))&CHAR(97+MOD(ROW()-1,8))

7. ## Re: Sequential Alphanumeric entries

This almost works but I need the first character, in this case A to change to B after the 2nd character has reached 6. So the next code after A6H would be B1A, then again through to B6H, when the code will change to C1A and so on. Sorry if I'm not making it too clear but thanks for your help.

8. ## Re: Sequential Alphanumeric entries

This is way over my head but thanks for your response

9. ## Re: Sequential Alphanumeric entries

Fantastic, that's done the trick, thanks very much

10. ## Re: Sequential Alphanumeric entries

Not entirely sure which posters you were responding to and which solution(s) worked.

However, you're welcome

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

11. ## Re: Sequential Alphanumeric entries

Sorry, still trying to meet a deadline so didn't notice the replies were generic and not linked to individual posts.

Thanks to everyone who answered, I'm blown away with the speed you guys replied to this. I used Special-K's formula in the end.

12. ## Re: Sequential Alphanumeric entries

OK, good. If you need that in Upper Case, as in your example:

Formula:
`Please Login or Register  to view this content.`

Regards, TMS

##### Users Browsing this Thread

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

#### 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