I need to replace the last 3 characters of a cell's content based on the value in another cell
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
microsoft-excel
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
asked Jul 25 '13 at 14:07
ADRADR
612
add a comment |
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
microsoft-excel
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
asked Jul 25 '13 at 14:07
ADRADR
612
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 ColumnsAandB. Rows have numbers.
– Scott
Jul 26 '13 at 1:55
add a comment |
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
microsoft-excel
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
asked Jul 25 '13 at 14:07
ADRADR
612
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
microsoft-excel
microsoft-excel
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
asked Jul 25 '13 at 14:07
ADRADR
612
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
asked Jul 25 '13 at 14:07
ADRADR
612
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
edited Jul 25 '13 at 14:20
Raystafarian
19.5k105189
19.5k105189
asked Jul 25 '13 at 14:07
ADRADR
612
asked Jul 25 '13 at 14:07
ADRADR
612
asked Jul 25 '13 at 14:07
ADRADR
612
612
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 ColumnsAandB. Rows have numbers.
– Scott
Jul 26 '13 at 1:55
add a comment |
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 ColumnsAandB. Rows have numbers.
– Scott
Jul 26 '13 at 1:55
You will probably need a macro for this, what have you tried already?
– Raystafarian
Jul 25 '13 at 14:21
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
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 a comment |
2 Answers
2
active
oldest
votes
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.
edited Jul 26 '13 at 1:59
Scott
16.2k113990
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
add a 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).
edited Mar 20 '17 at 10:17
Community♦
1
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f624114%2fi-need-to-replace-the-last-3-characters-of-a-cells-content-based-on-the-value-i%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
edited Jul 26 '13 at 1:59
Scott
16.2k113990
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
add a comment |
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.
edited Jul 26 '13 at 1:59
Scott
16.2k113990
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
add a comment |
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.
edited Jul 26 '13 at 1:59
Scott
16.2k113990
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
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.
edited Jul 26 '13 at 1:59
Scott
16.2k113990
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
edited Jul 26 '13 at 1:59
Scott
16.2k113990
edited Jul 26 '13 at 1:59
Scott
16.2k113990
edited Jul 26 '13 at 1:59
Scott
16.2k113990
16.2k113990
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
answered Jul 25 '13 at 14:30
TheCompWizTheCompWiz
8,58611619
8,58611619
add a comment |
add a 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).
edited Mar 20 '17 at 10:17
Community♦
1
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
add a 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).
edited Mar 20 '17 at 10:17
Community♦
1
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
add a 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).
edited Mar 20 '17 at 10:17
Community♦
1
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
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).
edited Mar 20 '17 at 10:17
Community♦
1
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
edited Mar 20 '17 at 10:17
Community♦
1
edited Mar 20 '17 at 10:17
Community♦
1
edited Mar 20 '17 at 10:17
Community♦
1
1
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
answered Jul 26 '13 at 1:55
ScottScott
16.2k113990
16.2k113990
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f624114%2fi-need-to-replace-the-last-3-characters-of-a-cells-content-based-on-the-value-i%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
AandB. Rows have numbers.– Scott
Jul 26 '13 at 1:55