Home |
Search |
Today's Posts |
#1
|
|||
|
|||
+1, Alpha *and* Numeric
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. |
#2
|
|||
|
|||
=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" wrote in message ... 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. |
#3
|
|||
|
|||
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote...
=CHAR(CODE(MID(A$1,1,1))+ROW(A1))&MID(A$1,2,1)+RO W(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? |
#4
|
|||
|
|||
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" wrote in message ... "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote... =CHAR(CODE(MID(A$1,1,1))+ROW(A1))&MID(A$1,2,1)+RO W(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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP WITH ALPHA NUMERIC | Excel Worksheet Functions | |||
Columns are now numeric, not alpha. how to change back? | Excel Discussion (Misc queries) | |||
Search string for alpha or numeric | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) |