+1, Alpha *and* Numeric

[Caroline]

Cell 1: A12345
Cell 2: B23456

All cells are in the same column (column 1). The formula goes like +1+1+1+1,
but I don't know how to make Excel change the alpha to the next letter. Excel
also goes a little crazy when it comes to going from 9 to 10 - it goes from 9
back to 0.

How do I create a formula which will cause the next cell to read: "C34567",
which will also work for making the 9 go to 10, and not 0?

Also, I am looking to NOT use Visual Basic to solve this problem - only
Excel formulas, nested or not.

[Vasant Nanavati]

=CHAR(CODE(MID(A$1,1,1))+ROW(A1))&MID(A$1,2,1)+ROW(A1)&MID(A$1,3,1)+ROW(A1)&
MID(A$1,4,1)+ROW(A1)&MID(A$1,5,1)+ROW(A1)&MID(A$1,6,1)+ROW(A1)

assuming your data starts in cell A1 with the entry "A12345."

--

Vasant


"Caroline" <[email protected]> wrote in message
news:[email protected]...
> Cell 1: A12345
> Cell 2: B23456
>
> All cells are in the same column (column 1). The formula goes like
+1+1+1+1,
> but I don't know how to make Excel change the alpha to the next letter.
Excel
> also goes a little crazy when it comes to going from 9 to 10 - it goes
from 9
> back to 0.
>
> How do I create a formula which will cause the next cell to read:
"C34567",
> which will also work for making the 9 go to 10, and not 0?
>
> Also, I am looking to NOT use Visual Basic to solve this problem - only
> Excel formulas, nested or not.

[Harlan Grove]

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote...
>=CHAR(CODE(MID(A$1,1,1))+ROW(A1))&MID(A$1,2,1)+ROW(A1)
>&MID(A$1,3,1)+ROW(A1)&MID(A$1,4,1)+ROW(A1)&MID(A$1,5,1)
>+ROW(A1)&MID(A$1,6,1)+ROW(A1)
>
>assuming your data starts in cell A1 with the entry "A12345."

Do you suppose the OP really wants to go from Z2627282930 in A26 to
[2728293031 in A27?

[Vasant Nanavati]

No, I just assumed that she didn't have more than 26 items as she didn't
specify what to do when one got past "Z.". Turns out it was the right
assumption, as I got an email from her stating that it "worked exactly as
[she] wanted it to."

--

Vasant


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote...
> >=CHAR(CODE(MID(A$1,1,1))+ROW(A1))&MID(A$1,2,1)+ROW(A1)
> >&MID(A$1,3,1)+ROW(A1)&MID(A$1,4,1)+ROW(A1)&MID(A$1,5,1)
> >+ROW(A1)&MID(A$1,6,1)+ROW(A1)
> >
> >assuming your data starts in cell A1 with the entry "A12345."
>
> Do you suppose the OP really wants to go from Z2627282930 in A26 to
> [2728293031 in A27?
>
>