Hi, how to make excel to copy following pattern? need numbers up to 76 or so.

[SoftKiller]

as above ;-)

UB01A
UB01B
UB02A
UB02B
UB03A
UB03B
UB04A
UB04B
UB05A
UB05B

Thank You :-)

PS. Alternatively how to replace letter B with C in the above string ;-)

[Pete_UK]

Put this formula in your starting cell (e.g. A1):

="UB"&TEXT(INT((ROWS($1:1)-1)/2)+1,"00")&CHAR(64+MOD(ROWS($1:1)-1,2)+1)

then copy down as far as you need to. You may need to use semicolons ( ; ) in the formula instead of commas ( , ), depending on your regional settings.

Hope this helps.

Pete

[SoftKiller]

Nice stuff Man! Will try it when get back to office ;-)

[jason.b75]

Alternatively how to replace letter B with C in the above string ;-)

Expanding on Pete's suggestion, with the prefix, UB in C1 and the suffixes, A,B and C in D1:D3, try this formula in A2 and fill down as needed.

=$C$1&TEXT(INT((ROWS(A$2:A2)-1)/ROWS(D$1:D$3))+1,"00")&INDEX($D$1:$D$3,MOD(ROWS(A$2:A2)-1,ROWS(D$1:D$3))+1)