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)
Also I don't want the characters to be more than 8 (including the 14k)
Thank you!
Would it be to much to ask if you could explain what exactly you did? I mean, how you compiled the formula?
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)
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
I know, but when I pull down B1 the numbers become 14kaa1010.. 1111 and 1212 and so on.
Ah I see, now it makes more sense haha. Thank you for the quick reply
Sorry I am unable to followI know, but when I pull down B1 the numbers become 14kaa1010.. 1111 and 1212 and so on.
Glad it helps you and thanks for the feedbackAh I see, now it makes more sense haha. Thank you for the quick reply
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Book1.xlsx
Here is an example
Please confirm you WANT or you DONT WANT???Also I don't want the characters to bemore than 8 (including the 14k)
I do not want the code to be greater than 8. Can be equal to 8 but not greater.
Dragging down from 9th row to 10 row will make it as two digit 10.
Like that it is based on the number of cells you apply the suggested formula it will go at a greater extent
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks