Function or formula to autofill letters in sequence

[fischXcell2]

Hi,
I need to create an inventory worksheet that includes the serial number and the item name. The Item name is easy because they are sequential digits, so I can just drag down, but that doesn't work with the serial numbers.
I searched for a function or formula to help with this and I found a post that was close to what I'm looking for, but not quite. In addition, I'm too much of a newbie to understand the suggested formulas completely. For example, I STILL don't understand the ADDRESS function even though I've read the description 3 times! I have to play around with it more to see if I can understand how it works. Same with SUBSTITUTE.

I understand the LEFT function and that helped to this point: =LEFT(A2, 6). This gave me ABC0DE, but then how do I add the A, B, C, D and so on. Is there a way to incorporate the =CHAR(64+ROW(1stCELL)) and somehow concatenate with the LEFT function?

Serial #	Item
ABC0DEA	3456
ABC0DEB	3457
ABC0DEC	3458
ABC0DED	3459

Thanks in advance.

 

[HighAndWilder]

Hi,
I need to create an inventory worksheet that includes the serial number and the item name. The Item name is easy because they are sequential digits, so I can just drag down, but that doesn't work with the serial numbers.
I searched for a function or formula to help with this and I found a post that was close to what I'm looking for, but not quite. In addition, I'm too much of a newbie to understand the suggested formulas completely. For example, I STILL don't understand the ADDRESS function even though I've read the description 3 times! I have to play around with it more to see if I can understand how it works. Same with SUBSTITUTE.

I understand the LEFT function and that helped to this point: =LEFT(A2, 6). This gave me ABC0DE, but then how do I add the A, B, C, D and so on. Is there a way to incorporate the =CHAR(64+ROW(1stCELL)) and somehow concatenate with the LEFT function?

Serial #	Item
ABC0DEA	3456
ABC0DEB	3457
ABC0DEC	3458
ABC0DED	3459

Thanks in advance.

You could do something like this but it does depend how many row you have.

This would work with 15682 rows.

After that you would need to change the first three digits.

Could you start with AAA0AAA?

If you want to start with a different last three characters then just change the number after the plus sign in the formula.

Checkbox to insert date in other column.xlsm
	A	B	C	D
1		Serial #	Item
2		ABC0AAA	3456
3		ABC0AAB	3457
4		ABC0AAC	3458
5		ABC0AAD	3459
6		ABC0AAE	3460
7		ABC0AAF	3461
8		ABC0AAG	3462
9		ABC0AAH	3463
10		ABC0AAI	3464
11		ABC0AAJ	3465
12		ABC0AAK	3466
13		ABC0AAL	3467
14		ABC0AAM	3468
15		ABC0AAN	3469
16		ABC0AAO	3470
17		ABC0AAP	3471
18		ABC0AAQ	3472
19		ABC0AAR	3473
20		ABC0AAS	3474
21		ABC0AAT	3475
22		ABC0AAU	3476
23		ABC0AAV	3477
24		ABC0AAW	3478
25		ABC0AAX	3479
26		ABC0AAY	3480
27		ABC0AAZ	3481
28		ABC0ABA	3482
29		ABC0ABB	3483
30		ABC0ABC	3484
31
Sheet2

Cell Formulas
Range	Formula
B2:B30	B2	="ABC0" & LEFT(ADDRESS(1,ROW()+701,4),3)
C3:C30	C3	=C2+1

 

[fischXcell2]

Thank you, @HighAndWilder for the quick response.
I'm sorry, I was not clear in my explanation. I'm not generating any of this information, I'm just entering data. It's just that it's a LOT of data, and it will take me forever to copy and paste the serial numbers and then add the letters after. That's why I was looking for an easier way to do this, since filling down doesn't work with letters.

 

[HighAndWilder]

I'm sorry, I was not clear in my explanation. I'm not generating any of this information, I'm just entering data. It's just that it's a LOT of data, and it will take me forever to copy and paste the serial numbers and then add the letters after. That's why I was looking for an easier way to do this, since filling down doesn't work with letters.

Now I don't understand. What are you adding to the serial numbers that already exist? Where are the additional letters before you copy and paste them?

 

[fischXcell2]

Now I don't understand. What are you adding to the serial numbers that already exist? Where are the additional letters before you copy and paste them?

I have a list (print out, not digital) with serial numbers and item names. It's a really LONG list. I have to enter the information on a spreadsheet.
Entering the item names is not a problem, because they are sequential numbers, so I just enter the first one and then drag down.
The serial numbers are a combination of numbers and letters. They have the same number/letter combination for about 7 places - as in my example: "ABC0DE" then comes either a number or a letter. The serial numbers that end in a number are also no problem, I can drag them down....but the ones that end in letters have to be entered manually, and it's TEDIOUS!!

I thought if I could find a way to combine the '=LEFT' function with the '=CHAR(64+ROW(1stCELL))' function, I can fill the serial numbers with letters at the end down, instead of having to enter them one by one. Or perhaps there's another way.....

Does that help to clarify my issue?

 

[HighAndWilder]

I have a list (print out, not digital) with serial numbers and item names. It's a really LONG list. I have to enter the information on a spreadsheet.
Entering the item names is not a problem, because they are sequential numbers, so I just enter the first one and then drag down.
The serial numbers are a combination of numbers and letters. They have the same number/letter combination for about 7 places - as in my example: "ABC0DE" then comes either a number or a letter. The serial numbers that end in a number are also no problem, I can drag them down....but the ones that end in letters have to be entered manually, and it's TEDIOUS!!

I thought if I could find a way to combine the '=LEFT' function with the '=CHAR(64+ROW(1stCELL))' function, I can fill the serial numbers with letters at the end down, instead of having to enter them one by one. Or perhaps there's another way.....

Does that help to clarify my issue?

So what is the sequence logic?

If is the first is serial number that you have and what would be the next 10?

What logic determines what the next suffix is (character or number)?

 

[fischXcell2]

So what is the sequence logic?

If is the first is serial number that you have and what would be the next 10?

What logic determines what the next suffix is (character or number)?

I see what you're asking. For the example ABC0DE, the sequence is 0-9, then comes A-Z, then the E changes to F, and we now have ABC0DF (0-9) and then A-Z, then the F changes to G and so on. I'm not concerned about the series that ends in numbers, because I can just enter the first number and drag to 9. I was just looking for a faster way to enter the series that end in letters. Like this:
ABC0DE0
ABC0DE1
ABC0DE2....ABC0E9,
ABC0DEA
ABC0DEB

*You know, even if there is no solution, I want to thank you for taking the time to try to help me. I really, really appreciate it.

 

[HighAndWilder]

I see what you're asking. For the example ABC0DE, the sequence is 0-9, then comes A-Z, then the E changes to F, and we now have ABC0DF (0-9) and then A-Z, then the F changes to G and so on. I'm not concerned about the series that ends in numbers, because I can just enter the first number and drag to 9. I was just looking for a faster way to enter the series that end in letters.

*You know, even if there is no solution, I want to thank you for taking the time to try to help me. I really, really appreciate it.

So what happens when the 5th character (D in your starting serial number) gets to Z and the 6th character gets to Z and the digit gets to 9. ABC0ZZ9. Do the serial numbers start at ABC0D0 or AAA0A0? What do they go up to as using this logic there is a limit?

 

[StephenCrump]

Will this help?

(Adapted from this thread: Increase letter and number sequence in order)

	A
1	ABC1DZ8
2	ABC1DZ9
3	ABC1DZA
4	ABC1DZB
5	ABC1DZC
6	ABC1DZD
7	ABC1DZE
8	ABC1DZF
9	ABC1DZG
10	ABC1DZH
11	ABC1DZI
12	ABC1DZJ
13	ABC1DZK
14	ABC1DZL
15	ABC1DZM
16	ABC1DZN
17	ABC1DZO
18	ABC1DZP
19	ABC1DZQ
20	ABC1DZR
21	ABC1DZS
22	ABC1DZT
23	ABC1DZU
24	ABC1DZV
25	ABC1DZW
26	ABC1DZX
27	ABC1DZY
28	ABC1DZZ
29	ABC1E00
30	ABC1E01
31	ABC1E02
32	ABC1E03
33	ABC1E04
34	ABC1E05
35	ABC1E06
36	ABC1E07
37	ABC1E08
38	ABC1E09
39	ABC1E0A
40	ABC1E0B
41	ABC1E0C
Sheet1

Cell Formulas
Range	Formula
A2:A41	A2	=LET(N,LEN(A1),c,CODE(MID(A1,SEQUENCE(,N),1)),d,DECIMAL(MID(BASE(1+DECIMAL(TEXTJOIN(,,BASE(c-IF(c<65,48,55),36)),36),36,N),SEQUENCE(,N),1),36),TEXTJOIN(,,CHAR(d+IF(d>9,55,48))))

 

[fischXcell2]

So what happens when the 5th character (D in your starting serial number) gets to Z and the 6th character gets to Z and the digit gets to 9. ABC0ZZ9. Do the serial numbers start at ABC0D0 or AAA0A0? What do they go up to as using this logic there is a limit?

Sorry for the delay, @HighAndWilder, I was away for a couple of days.

It's actually an 8 digit serial number. The first 4 digits in the list that I have do not change, so I guess the list won't be as long as I thought!
The next batch of items will either have completely different serial numbers or they will pick up where this current list leaves off. I'll have to wait and see.

That formula looks like it will be very helpful. Thank you so much. I'll try it out first thing tomorrow and then work on trying to understand it!

Best regards