Excel formula 3 letter but to include 1-9 as we

[Kerr3189]

Hi,
I’m looking for a formula that would be from aaa to z9z then start again.
So the middle letter goes from A-9 ( including 0 before 1) but doesn’t change until the last letter gets to Z
the last letter I just need to A to Z
and the same with the first letter but the first letter doesn’t change until the middle letter has gone to 9.
I’m also looking for it to be able to start from what ever sequence ( as having been doing manually and on BDZ) would also like to be able to put 170 at front of each 3 letter/number sequence.
Please help

 

[mackc557]

Someone else might have a more elegant solution, but this is mine. It does require a 6 character code to be right above the first instance of the code, and it automatically enters the "170" in front. If this doesn't work for you, please provide more information on what you are looking for.

="170" & IF(AND(MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),5,1)="9",RIGHT(INDIRECT(ADDRESS(ROW()-1,COLUMN())),1)="Z"),IF(MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),4,1)="Z","A",CHAR(CODE(MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),4,1))+1)),MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),4,1)) & IF(RIGHT(INDIRECT(ADDRESS(ROW()-1,COLUMN())),1)="Z",IF(MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),5,1)="Z","0",IF(MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),5,1)="9","A",CHAR(CODE(MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),5,1))+1))),MID(INDIRECT(ADDRESS(ROW()-1,COLUMN())),5,1)) & IF(RIGHT(INDIRECT(ADDRESS(ROW()-1,COLUMN())),1)="Z","A",CHAR(CODE(RIGHT(INDIRECT(ADDRESS(ROW()-1,COLUMN())),1))+1))

 

[Kerr3189]

Someone else might have a more elegant solution, but this is mine. It does require a 6 character code to be right above the first instance of the code, and it automatically enters the "170" in front. If this doesn't work for you, please provide more information on what you are looking for.

Hi that is so so close to be perfect thank you. The formula when input starts at 170BEA how do I get it to follow on from my previous manually input cell

 

[mackc557]

Hi that is so so close to be perfect thank you. The formula when input starts at 170BEA how do I get it to follow on from my previous manually input cell

A couple points here:
1. Place a 6 character string in a cell, then place this code in the cell below the manually entered string, then use the Fill Handle in the corner of the cell to expand downward.
2. This only works with downward expanding rows (the code can be modified to do rightward expanding columns by changing all of the Row()-1 to Row() and changing all the Column() to Column()-1 )
3. This code is set up to automatically add the "170" to the beginning, that can be changed to any set of 3 characters or can copy the first 3 characters of the last row by replacing "170" (and its quotes) with LEFT(INDIRECT(ADDRESS(ROW()-1,COLUMN())),3) (again this can be changed to work rightward making the same change listed above
4. This does as you asked, 4th character is A-Z, 5th character is A-Z-0-9, 6th character is A-Z (I only point this out for anyone trying to copy this code and wondering why this is happening)

Let me know if this helps or not, or if there are any tweaks/correction that need to be made.

 

[Eric W]

Try:

Book1
	A	B	C
1
2	170BEA	170Z9X	170CZY
3	170BEB	170Z9Y	170CZZ
4	170BEC	170Z9Z	170C0A
5	170BED	170AAA	170C0B
6	170BEE	170AAB	170C0C
7	170BEF	170AAC	170C0D
8	170BEG	170AAD	170C0E
9	170BEH	170AAE	170C0F
10	170BEI	170AAF	170C0G
Sheet11

Cell Formulas
Range	Formula
A3:C10	A3	="170"& CHAR(MOD(CODE(MID(A2,4,1))-65+(RIGHT(A2,2)="9Z"),26)+65)& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789A",SEARCH(MID(A2,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")+(RIGHT(A2)="Z"),1)& CHAR(MOD(CODE(RIGHT(A2))-64,26)+65)

Put the starting value in the top row, then the formula below that. This formula will choose the next value based on what's above it.

 

[jasonb75]

@Eric W came up with a solution a bit quicker than me but I'll post mine anyway.

Book1
	A	B	C
2	170BDZ	170BZZ	170B9Z
3	170BEA	170B0A	170CAA
4	170BEB	170B0B	170CAB
5	170BEC	170B0C	170CAC
6	170BED	170B0D	170CAD
Sheet5

Cell Formulas
Range	Formula
A3:C6	A3	=LET(l,CODE(MID(A2,6,1)),lc,IF(l=90,65,l+1),m,CODE(MID(A2,5,1))+(lc=65),mc,IF(m=58,65,IF(m=91,48,m)),f,CODE(MID(A2,4,1))+AND(lc=65,mc=65),170&CHAR(f)&CHAR(mc)&CHAR(lc))

 

[mackc557]

Sorry all, I'm used to working with formulas that can figure out where they are and what's around them. All of my addressing is so you don't have to worry about which cell it's in.

 

[Kerr3189]

Thank you everyone for all the replies all solved

 

[Eric W]

Glad we could help! Thanks for the update.