How to autofill strings
 

Ok, autofill is used to sequence numbers in a row or column. How do I
autofill a column with a string sequence starting with 3 cells such as:
AL0176A
AL0176B
AL0176C
Ideally would want the result of values
AL0176D
AL0176E
AL0176F, etc.
I hope my meaning is clear! :)

How to autofill strings
 

Try this:
A10: ="AL0176"&CHAR(65+ROW()-10)
Copy down as far as needed

If you start the series in another cell...change the 10 to the row number of
the first cell of the series.

Example:
if the series starts on B5, then
B5:="AL0176"&CHAR(65+ROW()-5)

If you need "hardcoded" values, copy the range then PasteSpecialValues

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Allewyn" wrote:

Ok, autofill is used to sequence numbers in a row or column. How do I
autofill a column with a string sequence starting with 3 cells such as:
AL0176A
AL0176B
AL0176C
Ideally would want the result of values
AL0176D
AL0176E
AL0176F, etc.
I hope my meaning is clear! :)

How to autofill strings
 

Ok, but where do I type that, in the cell B5? There's no place to type it in
the pastespecialvalues dropdown

"Ron Coderre" wrote:

Try this:
A10: ="AL0176"&CHAR(65+ROW()-10)
Copy down as far as needed

If you start the series in another cell...change the 10 to the row number of
the first cell of the series.

Example:
if the series starts on B5, then
B5:="AL0176"&CHAR(65+ROW()-5)

If you need "hardcoded" values, copy the range then PasteSpecialValues

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Allewyn" wrote:

Ok, autofill is used to sequence numbers in a row or column. How do I
autofill a column with a string sequence starting with 3 cells such as:
AL0176A
AL0176B
AL0176C
Ideally would want the result of values
AL0176D
AL0176E
AL0176F, etc.
I hope my meaning is clear! :)

How to autofill strings
 

Can't do it with autofill, but you could use a formula, the general form
of which would be:

=LEFT(A1,6)&CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65)



In article ,
"Allewyn" wrote:

Ok, autofill is used to sequence numbers in a row or column. How do I
autofill a column with a string sequence starting with 3 cells such as:
AL0176A
AL0176B
AL0176C
Ideally would want the result of values
AL0176D
AL0176E
AL0176F, etc.
I hope my meaning is clear! :)

How to autofill strings
 

First, you put the formula in the first cell and press [Enter].
Second, you copy that formula down as far as you need it.

Next, you select from the first list cell through the last list cell.
Then, EditCopy.
Finally, EditPaste SpecialValues

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Allewyn" wrote:

Ok, but where do I type that, in the cell B5? There's no place to type it in
the pastespecialvalues dropdown

"Ron Coderre" wrote:

Try this:
A10: ="AL0176"&CHAR(65+ROW()-10)
Copy down as far as needed

If you start the series in another cell...change the 10 to the row number of
the first cell of the series.

Example:
if the series starts on B5, then
B5:="AL0176"&CHAR(65+ROW()-5)

If you need "hardcoded" values, copy the range then PasteSpecialValues

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Allewyn" wrote:

Ok, autofill is used to sequence numbers in a row or column. How do I
autofill a column with a string sequence starting with 3 cells such as:
AL0176A
AL0176B
AL0176C
Ideally would want the result of values
AL0176D
AL0176E
AL0176F, etc.
I hope my meaning is clear! :)

How to autofill strings
 

Oops, pasted the wrong formula in - this one will increment the numeric
digits when the right-most character gets to "Z":

=LEFT(A1,2) & TEXT(MID(A1,3,4)+(RIGHT(A1,1)="Z"),"0000") &
CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65)

Copy down as far as necessary.

In article ,
JE McGimpsey wrote:

Can't do it with autofill, but you could use a formula, the general form
of which would be:

=LEFT(A1,6)&CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65)

How to autofill strings
 

OK, I'm not understanding this but here's what I did:
al0176a
Al0176b
al0176c
al0176d al0176H
al0176e Al0176I
al0176f al0176J
al0176g al0176K
al0176L
al0176M
al0176N
#VALUE!
#VALUE!
#VALUE!
I originally had only al0176a - c maunally typed.
Then I pasted the formula into cell b4 and dragged it down 9 cells, with the
result of al0176H in cell b4 and #value! in the 9 cells under it. Then I
experimented by typing in al0176d in column A and noticed the other column
(cell b5) changed to al0176I. I then typed al0176E in the next cell down of
coulmn A and noticed a change to al0176J in b6. Each time I typed a vlue into
caolumn A, the next cell down changed to the next value sequentially. If I
haven't completely garbled this communication, (heh) is this what should
happen? Main question now: why did b4 receive the value "H" when the last
typed value was "C"?

Is there a place to read up on this?

"JE McGimpsey" wrote:

Oops, pasted the wrong formula in - this one will increment the numeric
digits when the right-most character gets to "Z":

=LEFT(A1,2) & TEXT(MID(A1,3,4)+(RIGHT(A1,1)="Z"),"0000") &
CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65)

Copy down as far as necessary.

In article ,
JE McGimpsey wrote:

Can't do it with autofill, but you could use a formula, the general form
of which would be:

=LEFT(A1,6)&CHAR(MOD(CODE(RIGHT(A1,1))-64,26)+65)