# I need to replace the last 3 characters of a cell's content based on the value in another cell

Column 1 contains values like the following:

``````AAA
AAB
AABAAA
AABAAB
AABAAC
AABAACAAA
``````

Column 2 contains numbers such as 1, 2, 3, 4, 5, 6

I need to change the last 3 characters in column 1 based on the value in column 2

The rule is 1 = AAA, 2 = AAB, 3 = AAC, 4 = AAD, etc.....

For example: if Column 1 contains AABAAC and the value in Column 2 is 1, I need to change the last 3 characters in Column 1 to AAA. The result for column 1 would then be AABAAA

-
You will probably need a macro for this, what have you tried already? – Raystafarian Jul 25 '13 at 14:21
It might help us to know what version of Excel you have. (2003? 2007?) And by the way, Excel doesn’t have Columns 1 and 2; it has Columns `A` and `B`. Rows have numbers. – Scott Jul 26 '13 at 1:55

There are a few ways to accomplish this... but I would recommend having a lookup sheet someplace in case you need to modify the values that the numbers correspond to:

In your Column 1, the value should be something like this:

``````="AAA AAB AABAAA AABAAB AABAAC AABAAC" & LOOKUP(B1,Sheet2!A\$1:A\$9999,Sheet2!B\$1:B\$9999)
``````

On sheet 2, you create something for the formula to lookup like this:

...etc., until you hit however many values you might want to look up.

I think this is the easiest method... but some alternative methods could include relying on the CHAR() function which could take the numeric value, add 40, and get the character going from A-Z... assuming you use numeric values 1-26... Or you could go the more-extreme route and create a function in VBA that would render however you saw fit. The last method is probably not the best idea as you would have to jump through a bunch more hoops to make it work.

-

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, set `C1` to

``````=LEFT(A1, LEN(A1)-3)  &  VLOOKUP(B1, Sheet2!A\$1:B\$9999, 2)
``````

and drag/fill down.  `LEFT(``X``, LEN(``X``) - 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)`

where `ADDRESS` is the function to turn coordinates into a cell address.  For example, `ADDRESS(4, 2, …)` returns `B4`, and `ADDRESS(4, 27, …)` returns `AA4`.  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 `AAA1`, and `LEFT(``string``, 3)` returns the first (leftmost) three characters of `string`.  Obviously you would plug in `B1` for `n`.

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).

-