Excel: Search and Replace based on Rules
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a list in Excel more than 3000 rows that I would like to research and replace some words within the list based on another list.
For example:
List 1:
Blackcurrant Juice 500ml
Strawberry Juice 750ml
List 2:
have two columns (a: the word that I want to replace, b: the word that I want to replace with)
A: Blackcurrant should be B: B/currant
A: Strawberry should be B: Strawb
And I want search in list 1 and replace the some word based on List two
Any ideas?
microsoft-excel
edited Sep 14 '13 at 10:25
wonea
1,48211940
asked Aug 27 '13 at 8:53
PisaroPisaro
612
add a comment |
I have a list in Excel more than 3000 rows that I would like to research and replace some words within the list based on another list.
For example:
List 1:
Blackcurrant Juice 500ml
Strawberry Juice 750ml
List 2:
have two columns (a: the word that I want to replace, b: the word that I want to replace with)
A: Blackcurrant should be B: B/currant
A: Strawberry should be B: Strawb
And I want search in list 1 and replace the some word based on List two
Any ideas?
microsoft-excel
edited Sep 14 '13 at 10:25
wonea
1,48211940
asked Aug 27 '13 at 8:53
PisaroPisaro
612
I can do a Vlookup with two table and exact match but I don't know how to look a word within a cell and then replace it based on another list using Vlookups
– Pisaro
Aug 27 '13 at 9:01
I assigned VBA because it thought it can be done with a Macro but I have ammended the post. List 1 is only one colume containing a brand explanation (e.g Blackcurrant Juice 500ml) but i have in another sheet two columns (it will be always two columns). column1: containing the word that I want to replace (e.g Blackcurrant) and Column2: the word that I want to replace with (e.g B/currant).
– Pisaro
Aug 27 '13 at 9:09
Did you perhaps see my answer? Is it perhaps not working as intended?
– Jerry
Aug 27 '13 at 20:09
add a comment |
I have a list in Excel more than 3000 rows that I would like to research and replace some words within the list based on another list.
For example:
List 1:
Blackcurrant Juice 500ml
Strawberry Juice 750ml
List 2:
have two columns (a: the word that I want to replace, b: the word that I want to replace with)
A: Blackcurrant should be B: B/currant
A: Strawberry should be B: Strawb
And I want search in list 1 and replace the some word based on List two
Any ideas?
microsoft-excel
edited Sep 14 '13 at 10:25
wonea
1,48211940
asked Aug 27 '13 at 8:53
PisaroPisaro
612
I have a list in Excel more than 3000 rows that I would like to research and replace some words within the list based on another list.
For example:
List 1:
Blackcurrant Juice 500ml
Strawberry Juice 750ml
List 2:
have two columns (a: the word that I want to replace, b: the word that I want to replace with)
A: Blackcurrant should be B: B/currant
A: Strawberry should be B: Strawb
And I want search in list 1 and replace the some word based on List two
Any ideas?
microsoft-excel
microsoft-excel
edited Sep 14 '13 at 10:25
wonea
1,48211940
asked Aug 27 '13 at 8:53
PisaroPisaro
612
edited Sep 14 '13 at 10:25
wonea
1,48211940
asked Aug 27 '13 at 8:53
PisaroPisaro
612
edited Sep 14 '13 at 10:25
wonea
1,48211940
edited Sep 14 '13 at 10:25
wonea
1,48211940
edited Sep 14 '13 at 10:25
wonea
1,48211940
1,48211940
asked Aug 27 '13 at 8:53
PisaroPisaro
612
asked Aug 27 '13 at 8:53
PisaroPisaro
612
asked Aug 27 '13 at 8:53
PisaroPisaro
612
612
I can do a Vlookup with two table and exact match but I don't know how to look a word within a cell and then replace it based on another list using Vlookups
– Pisaro
Aug 27 '13 at 9:01
I assigned VBA because it thought it can be done with a Macro but I have ammended the post. List 1 is only one colume containing a brand explanation (e.g Blackcurrant Juice 500ml) but i have in another sheet two columns (it will be always two columns). column1: containing the word that I want to replace (e.g Blackcurrant) and Column2: the word that I want to replace with (e.g B/currant).
– Pisaro
Aug 27 '13 at 9:09
Did you perhaps see my answer? Is it perhaps not working as intended?
– Jerry
Aug 27 '13 at 20:09
add a comment |
I can do a Vlookup with two table and exact match but I don't know how to look a word within a cell and then replace it based on another list using Vlookups
– Pisaro
Aug 27 '13 at 9:01
I assigned VBA because it thought it can be done with a Macro but I have ammended the post. List 1 is only one colume containing a brand explanation (e.g Blackcurrant Juice 500ml) but i have in another sheet two columns (it will be always two columns). column1: containing the word that I want to replace (e.g Blackcurrant) and Column2: the word that I want to replace with (e.g B/currant).
– Pisaro
Aug 27 '13 at 9:09
Did you perhaps see my answer? Is it perhaps not working as intended?
– Jerry
Aug 27 '13 at 20:09
I can do a Vlookup with two table and exact match but I don't know how to look a word within a cell and then replace it based on another list using Vlookups
– Pisaro
Aug 27 '13 at 9:01
I can do a Vlookup with two table and exact match but I don't know how to look a word within a cell and then replace it based on another list using Vlookups
– Pisaro
Aug 27 '13 at 9:01
I assigned VBA because it thought it can be done with a Macro but I have ammended the post. List 1 is only one colume containing a brand explanation (e.g Blackcurrant Juice 500ml) but i have in another sheet two columns (it will be always two columns). column1: containing the word that I want to replace (e.g Blackcurrant) and Column2: the word that I want to replace with (e.g B/currant).
– Pisaro
Aug 27 '13 at 9:09
I assigned VBA because it thought it can be done with a Macro but I have ammended the post. List 1 is only one colume containing a brand explanation (e.g Blackcurrant Juice 500ml) but i have in another sheet two columns (it will be always two columns). column1: containing the word that I want to replace (e.g Blackcurrant) and Column2: the word that I want to replace with (e.g B/currant).
– Pisaro
Aug 27 '13 at 9:09
Did you perhaps see my answer? Is it perhaps not working as intended?
– Jerry
Aug 27 '13 at 20:09
Did you perhaps see my answer? Is it perhaps not working as intended?
– Jerry
Aug 27 '13 at 20:09
add a comment |
2 Answers
2
active
oldest
votes
Once you run the VLOOKUP, you can use substitute:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), B1, 1)
A1 is the cell containing Blackcurrant Juice 500ml, B1 is the cell containing B/currant after VLOOKUP was run.
Or you can combine both formulas as below:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), VLOOKUP(LEFT(A1, FIND(" ",A1)-1), Sheet2!A:B, 2, 0), 1)
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
add a comment |
It's hard to know what your criteria is, but, I've got enough here I hope to get you going.
Any way, my first sheet has
And my second sheet has
Then, on my first sheet, in column B I have the following code
=VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE)
And a screen shot to show what column B now displays
Update
Update function to
=CONCATENATE(VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE), RIGHT(A3, SEARCH(" ",A3)-1))
So it will display B/currant juice 500ml
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
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%2f637147%2fexcel-search-and-replace-based-on-rules%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
Once you run the VLOOKUP, you can use substitute:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), B1, 1)
A1 is the cell containing Blackcurrant Juice 500ml, B1 is the cell containing B/currant after VLOOKUP was run.
Or you can combine both formulas as below:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), VLOOKUP(LEFT(A1, FIND(" ",A1)-1), Sheet2!A:B, 2, 0), 1)
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
add a comment |
Once you run the VLOOKUP, you can use substitute:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), B1, 1)
A1 is the cell containing Blackcurrant Juice 500ml, B1 is the cell containing B/currant after VLOOKUP was run.
Or you can combine both formulas as below:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), VLOOKUP(LEFT(A1, FIND(" ",A1)-1), Sheet2!A:B, 2, 0), 1)
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
add a comment |
Once you run the VLOOKUP, you can use substitute:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), B1, 1)
A1 is the cell containing Blackcurrant Juice 500ml, B1 is the cell containing B/currant after VLOOKUP was run.
Or you can combine both formulas as below:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), VLOOKUP(LEFT(A1, FIND(" ",A1)-1), Sheet2!A:B, 2, 0), 1)
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
Once you run the VLOOKUP, you can use substitute:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), B1, 1)
A1 is the cell containing Blackcurrant Juice 500ml, B1 is the cell containing B/currant after VLOOKUP was run.
Or you can combine both formulas as below:
=SUBSTITUTE(A1, LEFT(A1, FIND(" ",A1)-1), VLOOKUP(LEFT(A1, FIND(" ",A1)-1), Sheet2!A:B, 2, 0), 1)
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
answered Aug 27 '13 at 11:04
JerryJerry
4,652928
4,652928
add a comment |
add a comment |
It's hard to know what your criteria is, but, I've got enough here I hope to get you going.
Any way, my first sheet has
And my second sheet has
Then, on my first sheet, in column B I have the following code
=VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE)
And a screen shot to show what column B now displays
Update
Update function to
=CONCATENATE(VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE), RIGHT(A3, SEARCH(" ",A3)-1))
So it will display B/currant juice 500ml
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
add a comment |
It's hard to know what your criteria is, but, I've got enough here I hope to get you going.
Any way, my first sheet has
And my second sheet has
Then, on my first sheet, in column B I have the following code
=VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE)
And a screen shot to show what column B now displays
Update
Update function to
=CONCATENATE(VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE), RIGHT(A3, SEARCH(" ",A3)-1))
So it will display B/currant juice 500ml
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
add a comment |
It's hard to know what your criteria is, but, I've got enough here I hope to get you going.
Any way, my first sheet has
And my second sheet has
Then, on my first sheet, in column B I have the following code
=VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE)
And a screen shot to show what column B now displays
Update
Update function to
=CONCATENATE(VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE), RIGHT(A3, SEARCH(" ",A3)-1))
So it will display B/currant juice 500ml
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
It's hard to know what your criteria is, but, I've got enough here I hope to get you going.
Any way, my first sheet has
And my second sheet has
Then, on my first sheet, in column B I have the following code
=VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE)
And a screen shot to show what column B now displays
Update
Update function to
=CONCATENATE(VLOOKUP(LEFT(A3,SEARCH(" ",A3)-1),Sheet2!$A$1:$B$2, 2, FALSE), RIGHT(A3, SEARCH(" ",A3)-1))
So it will display B/currant juice 500ml
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
edited Aug 27 '13 at 11:22
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
answered Aug 27 '13 at 10:27
DaveDave
23.4k74463
23.4k74463
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
add a comment |
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
I think it works if the word that I want to replace is at the beginning of the sentence. This is a good start but the ultimate goals is replace the word blackcurrant with the B/currant
– Pisaro
Aug 27 '13 at 11:11
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
@Pisaro Updated my post. It now displays the full string.
– Dave
Aug 27 '13 at 11:23
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%2f637147%2fexcel-search-and-replace-based-on-rules%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
I can do a Vlookup with two table and exact match but I don't know how to look a word within a cell and then replace it based on another list using Vlookups
– Pisaro
Aug 27 '13 at 9:01
I assigned VBA because it thought it can be done with a Macro but I have ammended the post. List 1 is only one colume containing a brand explanation (e.g Blackcurrant Juice 500ml) but i have in another sheet two columns (it will be always two columns). column1: containing the word that I want to replace (e.g Blackcurrant) and Column2: the word that I want to replace with (e.g B/currant).
– Pisaro
Aug 27 '13 at 9:09
Did you perhaps see my answer? Is it perhaps not working as intended?
– Jerry
Aug 27 '13 at 20:09