What do you mean by “change”? Is it good enough to create a new column
that contains the manipulated values? If so, set up the lookup table
as described by TheCompWiz,
=LEFT(A1, LEN(A1)-3) & VLOOKUP(B1, Sheet2!A$1:B$9999, 2)
and drag/fill down.
) - 3) is all of
X except for the 3 right-most characters, and
& is string concatenation.
The question says,
The rule is 1 = AAA, 2 = AAB, 3 = AAC, 4 = AAD, etc...
This looks like a sort of base-26 notation.
I guess 26 = AAZ, 27 = ABA, 260 = AJZ, 261 = AKA, 676 (26 × 26) = AZZ, 677 = BAA, etc.
Coincidentally, this looks a lot like the way Excel labels columns.
If you have a sufficiently current version of Excel (2007? 2010?) you can get this with the formula
LEFT(ADDRESS(1, 26*26 + 26 +n
, 4), 3)
ADDRESS is the function to turn coordinates into a cell address.
ADDRESS(4, 2, …) returns
ADDRESS(4, 27, …) returns
The third parameter specifies whether to use absolute or relative addressing;
4 means row and column are relative. So
ADDRESS(1, 26*26 + 26 + 1, 4) is
, 3) returns the first (leftmost) three characters of
Obviously you would plug in
P.S. You need the current version of Excel because in Excel 2003 and earlier (and I’m not sure about 2007), a worksheet can have at most 256 columns, and the
ADDRESS function won’t left you generate the name of a cell that can’t exist (i.e., one for which the column number > 256).