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?

edited Sep 14 '13 at 10:25

wonea

1,48211940

asked Aug 27 '13 at 8:53

Pisaro

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?

edited Sep 14 '13 at 10:25

wonea

1,48211940

asked Aug 27 '13 at 8:53

Pisaro

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?

edited Sep 14 '13 at 10:25

wonea

1,48211940

asked Aug 27 '13 at 8:53

Pisaro

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

edited Sep 14 '13 at 10:25

wonea

1,48211940

asked Aug 27 '13 at 8:53

Pisaro

612

edited Sep 14 '13 at 10:25

wonea

1,48211940

asked Aug 27 '13 at 8:53

Pisaro

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

asked Aug 27 '13 at 8:53

Pisaro

612

asked Aug 27 '13 at 8:53

Pisaro

612

asked Aug 27 '13 at 8:53

Pisaro

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

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

Jerry

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

enter image description here

And my second sheet has

enter image description here

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

enter image description here

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

edited Aug 27 '13 at 11:22

answered Aug 27 '13 at 10:27

Dave

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

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

Jerry

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

Jerry

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

Jerry

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

Jerry

4,652928

answered Aug 27 '13 at 11:04

Jerry

4,652928

answered Aug 27 '13 at 11:04

Jerry

4,652928

answered Aug 27 '13 at 11:04

Jerry

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

enter image description here

And my second sheet has

enter image description here

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

enter image description here

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

edited Aug 27 '13 at 11:22

answered Aug 27 '13 at 10:27

Dave

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

enter image description here

And my second sheet has

enter image description here

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

enter image description here

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

edited Aug 27 '13 at 11:22

answered Aug 27 '13 at 10:27

Dave

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

enter image description here

And my second sheet has

enter image description here

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

enter image description here

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

edited Aug 27 '13 at 11:22

answered Aug 27 '13 at 10:27

Dave

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

enter image description here

And my second sheet has

enter image description here

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

enter image description here

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

edited Aug 27 '13 at 11:22

answered Aug 27 '13 at 10:27

Dave

23.4k74463

edited Aug 27 '13 at 11:22

answered Aug 27 '13 at 10:27

Dave

23.4k74463

answered Aug 27 '13 at 10:27

Dave

23.4k74463

answered Aug 27 '13 at 10:27

Dave

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

@Pisaro Updated my post. It now displays the full string.

– Dave
Aug 27 '13 at 11:23

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr