How to autofill strings

[Allewyn]

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!

[Ron Coderre]

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 Paste>Special>Values

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!
>

[Allewyn]

Ok, but where do I type that, in the cell B5? There's no place to type it in
the paste>special>values 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 Paste>Special>Values
>
> 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!
> >

[Ron Coderre]

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, Edit>Copy.
Finally, Edit>Paste Special>Values

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 paste>special>values 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 Paste>Special>Values
> >
> > 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!
> > >

[JE McGimpsey]

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 <300798FA-50A4-4EB8-AF99-D10386081E28@microsoft.com>,
"Allewyn" <Allewyn@discussions.microsoft.com> 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!

[JE McGimpsey]

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 <jemcgimpsey-B0B79F.09154809012006@msnews.microsoft.com>,
JE McGimpsey <jemcgimpsey@mvps.org> 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)
>
>

[Allewyn]

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 <jemcgimpsey-B0B79F.09154809012006@msnews.microsoft.com>,
> JE McGimpsey <jemcgimpsey@mvps.org> 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)
> >
> >
>