Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

2 Answers

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:

Excel Screenshot

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.