Alphanumeric autofill formula

**frosty350** · 03-28-2013, 10:36 AM

Is there a way to autofill a six digit alphanumeric (C01A01)
I want the last 2 digits to go form 01-07 then change the last letter
next column same thing but first two numbers 02 and so on

C01A01 C02A01 C03A01
C01A02 C02A02 C03A02
C01A03 C02A03 C03A03
C01A04 C02A04 C03A04
C01A05 C02A05 C03A05
C01A06 C02A06 C03A06
C01A07 C02A07 C03A07
C01B01 C02B01 C03B01
C01B02 C02B02 C03B02
C01B03 C02B03 C03B03
C01B04 C02B04 C03B04
C01B05 C02B05 C03B05
C01B06 C02B06 C03B06
C01B07 C02B07 C03B07

is there a way for this to be autofilled

thanks

**NBVC** · 03-28-2013, 10:58 AM

Assuming starting in A1, enter formula:

="C0"&COLUMN()&SUBSTITUTE(ADDRESS(1,MOD(INT((ROW()-ROW($A$1))/7),7)+1,4),1,"")&TEXT(MOD(ROW()-ROW($A$1),7)+1,"00")

copied down and across.

if starting elsewhere enter A1 between the COLUMN() and ROW() references.

**frosty350** · 03-28-2013, 11:00 AM

sweet
it works

thanks

**NBVC** · 03-28-2013, 11:20 AM

No problem

**frosty350** · 04-02-2013, 05:06 PM

Ok I need a tweak to this formula

Need to change the last two numbers from 01-07 to 01-05
and change the second letter from A-G to A-I

Thanks

**NBVC** · 04-04-2013, 04:43 PM

Sorry for the late help... try:

="C0"&COLUMN()&SUBSTITUTE(ADDRESS(1,MOD(INT((ROW()-ROW($A$1))/5),9)+1,4),1,"")&TEXT(MOD(ROW()-ROW($A$1),5)+1,"00")

Alphanumeric autofill formula

LinkBack

Thread Tools

Rate This Thread

Display

Alphanumeric autofill formula

Re: Alphanumeric autofill formula

Re: Alphanumeric autofill formula

Re: Alphanumeric autofill formula

Re: Alphanumeric autofill formula

Re: Alphanumeric autofill formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions