Creating a series of cells with multiple incrimenting characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

markb - 29 Jun 2007 18:36 GMT

Hi,

I need to create a series of 1000 cells down a column. They will be in this
format:

MM1A
MM1B
MM1C
MM1D
MM1E
MM3A
MM3B
MM3C
MM3D
MM3E
MM5A
MM5B
MM5C
.....

So, the first 2 characters are always the same, the 3rd character, an odd
number incriments by 2 every 6th row, and the fourth character changes
alphabetically through 5 rows, then it resets to "A"...

Please give me some ideas?

Thanks,

Signature

Markb

Reply to this Message

Elkar - 29 Jun 2007 19:04 GMT

Here's a formula that will do what you've described, if I read it correctly.
Assuming you're starting in cell A1.

In A1 enter:
MM1A

IN A2 enter:
=IF(RIGHT(A1,1)<>"E",LEFT(A1,3)&CHAR(CODE(RIGHT(A1,1))+1),"MM"&MOD(MID(A1,3,1)+2,10)&"A")

Copy down through A1000

Note that the sequence repeats every 25 rows. Is this what you're looking
for? If so, it seems it would have been easier to just manually type out the
25 rows, then fill down through 1000.

HTH,
Elkar

> Hi,
>
[quoted text clipped - 23 lines]
>
> Thanks,

Reply to this Message

Teethless mama - 30 Jun 2007 04:50 GMT

Try this:

="MM"&INT((ROWS($1:1)-1)/5)*2+1&CHAR(MOD(ROWS($1:1)-1,5)+65)

copy down as far as needed

> Hi,
>
[quoted text clipped - 23 lines]
>
> Thanks,

Reply to this Message