Function or formula to autofill letters in sequence

fischXcell2

New Member
Joined
May 24, 2024
Messages
9
Office Version
  1. 2024
  2. 2021
Platform
  1. Windows
  2. MacOS
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
ABC0DEA3456
ABC0DEB3457
ABC0DEC3458
ABC0DED3459

Thanks in advance.
 
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
ABC0DEA3456
ABC0DEB3457
ABC0DEC3458
ABC0DED3459

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.

Cell Formulas
RangeFormula
B2:B30B2="ABC0" & LEFT(ADDRESS(1,ROW()+701,4),3)
C3:C30C3=C2+1
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
Will this help?

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

Cell Formulas
RangeFormula
A2:A41A2=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))))
 
Upvote 0
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! :biggrin:

Best regards
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top