Formula to Generate a list of codes
Hi, I am trying to generate a set of codes in alphabetical and numeral order. Example: 14kaa111, 14kbb111, 14kcc111 and then continue to become: 14kaa222, 14kaa333... and so on..
Can this be done? I have tried to type in 14kaa111, 14kbb111 and 14kcc111 in the first 3 cells and then extending them from there but it does not seem to work.
Any ideas?
Here is an example of what I am trying to get:
Book1.xlsx
In A1 Cell
="14k"&REPT(CHAR(96+ROW(1:1)),2)&REPT(1,3)
In B1 Cell
="14k"&REPT(CHAR(96+ROW(1:1)),2)&REPT(ROW(2:2),3)
Drag both the formula's down...
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Wow thank you! It works perfectly.
If I wanted to change the 14kaa111, 14kbb111
to
14kaa111, 14kab111, 14kac111...
What should I change in the formula?
Thank you so much again
Try this...
="14k"&CHAR(97)&CHAR(96+ROW(1:1))&REPT(1,3)
Thank you!
Would it be to much to ask if you could explain what exactly you did? I mean, how you compiled the formula?
Enter this in a cell and check what result it's getting
=CODE("a")
In Another cell
=CODE("b")
So the code number 97 belongs to lower case "a".
Adding +1 to 97 will make it as 98. Which is the code number of "b"
Enter this in a cell and check what result it's getting
=ROW(1:1)
Result will be 1
Drag it down and check what it's doing.
When you drag the above formula down then it will get adjusted to
=ROW(2:2)
Which results 2.
Enter this in a cell and check what result it's getting
=REPT(Text,NumberOfTimeItNeedsToBeRepeated)
=REPT("Animella",3)
Will result AnimellaAnimellaAnimella
Anything withing Double Quotes is a stable value "14k"
So just given the formula based on the above usages
Also I don't want the characters to be more than 8 (including the 14k)
But the examples which you shown is not more than 8 charactersOriginally Posted by Animella
Also I don't want the characters to be more than 8 (including the 14k)
I know, but when I pull down B1 the numbers become 14kaa1010.. 1111 and 1212 and so on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks