Excel: Remove only true cell content if its in a other cell
Cell A1: House of Cards
Cell B1: of Cards
Cell C1: EXPECTED RESULT House
Cell A2: Looking forward to hear
Cell B2: to hear
Cell C2: EXPECTED RESULT Looking forward
So, C1 should basically be the result of A1 minus exact content of B1.
and so on.
Any idea how to achieve this?
microsoft-excel
edited Jan 7 at 16:23
Run5k
10.8k73051
asked Jan 7 at 16:07
DanTheManDanTheMan
32
add a comment |
Cell A1: House of Cards
Cell B1: of Cards
Cell C1: EXPECTED RESULT House
Cell A2: Looking forward to hear
Cell B2: to hear
Cell C2: EXPECTED RESULT Looking forward
So, C1 should basically be the result of A1 minus exact content of B1.
and so on.
Any idea how to achieve this?
microsoft-excel
edited Jan 7 at 16:23
Run5k
10.8k73051
asked Jan 7 at 16:07
DanTheManDanTheMan
32
Likely going to need VBA. Must the text be in order? What ifA1isThe theory is the key, and inB1isthe. Do you want bothTheremoved? What have you tried?
– BruceWayne
Jan 7 at 16:11
yes...always the individual true value which is next to should be the one which should be removed. if needs to be done via VBA, any idea how?
– DanTheMan
Jan 7 at 16:15
3
Can you clarify with more examples? IfA1isLooking forward to hear that and hear it wellandB1isto hear, what should the output be?Looking forward that and hear it well? Or do you want to findtoandhearseparately?
– BruceWayne
Jan 7 at 16:25
1
Please edit your question to clarify. This may also prove useful for providing a good example.
– cybernetic.nomad
Jan 7 at 16:28
add a comment |
Cell A1: House of Cards
Cell B1: of Cards
Cell C1: EXPECTED RESULT House
Cell A2: Looking forward to hear
Cell B2: to hear
Cell C2: EXPECTED RESULT Looking forward
So, C1 should basically be the result of A1 minus exact content of B1.
and so on.
Any idea how to achieve this?
microsoft-excel
edited Jan 7 at 16:23
Run5k
10.8k73051
asked Jan 7 at 16:07
DanTheManDanTheMan
32
Cell A1: House of Cards
Cell B1: of Cards
Cell C1: EXPECTED RESULT House
Cell A2: Looking forward to hear
Cell B2: to hear
Cell C2: EXPECTED RESULT Looking forward
So, C1 should basically be the result of A1 minus exact content of B1.
and so on.
Any idea how to achieve this?
microsoft-excel
microsoft-excel
edited Jan 7 at 16:23
Run5k
10.8k73051
asked Jan 7 at 16:07
DanTheManDanTheMan
32
edited Jan 7 at 16:23
Run5k
10.8k73051
asked Jan 7 at 16:07
DanTheManDanTheMan
32
edited Jan 7 at 16:23
Run5k
10.8k73051
edited Jan 7 at 16:23
Run5k
10.8k73051
edited Jan 7 at 16:23
Run5k
10.8k73051
10.8k73051
asked Jan 7 at 16:07
DanTheManDanTheMan
32
asked Jan 7 at 16:07
DanTheManDanTheMan
32
asked Jan 7 at 16:07
DanTheManDanTheMan
32
32
Likely going to need VBA. Must the text be in order? What ifA1isThe theory is the key, and inB1isthe. Do you want bothTheremoved? What have you tried?
– BruceWayne
Jan 7 at 16:11
yes...always the individual true value which is next to should be the one which should be removed. if needs to be done via VBA, any idea how?
– DanTheMan
Jan 7 at 16:15
3
Can you clarify with more examples? IfA1isLooking forward to hear that and hear it wellandB1isto hear, what should the output be?Looking forward that and hear it well? Or do you want to findtoandhearseparately?
– BruceWayne
Jan 7 at 16:25
1
Please edit your question to clarify. This may also prove useful for providing a good example.
– cybernetic.nomad
Jan 7 at 16:28
add a comment |
Likely going to need VBA. Must the text be in order? What ifA1isThe theory is the key, and inB1isthe. Do you want bothTheremoved? What have you tried?
– BruceWayne
Jan 7 at 16:11
yes...always the individual true value which is next to should be the one which should be removed. if needs to be done via VBA, any idea how?
– DanTheMan
Jan 7 at 16:15
3
Can you clarify with more examples? IfA1isLooking forward to hear that and hear it wellandB1isto hear, what should the output be?Looking forward that and hear it well? Or do you want to findtoandhearseparately?
– BruceWayne
Jan 7 at 16:25
1
Please edit your question to clarify. This may also prove useful for providing a good example.
– cybernetic.nomad
Jan 7 at 16:28
Likely going to need VBA. Must the text be in order? What if
A1 is The theory is the key, and in B1 is the. Do you want both The removed? What have you tried?– BruceWayne
Jan 7 at 16:11
Likely going to need VBA. Must the text be in order? What if
A1 is The theory is the key, and in B1 is the. Do you want both The removed? What have you tried?– BruceWayne
Jan 7 at 16:11
yes...always the individual true value which is next to should be the one which should be removed. if needs to be done via VBA, any idea how?
– DanTheMan
Jan 7 at 16:15
yes...always the individual true value which is next to should be the one which should be removed. if needs to be done via VBA, any idea how?
– DanTheMan
Jan 7 at 16:15
3
3
Can you clarify with more examples? If
A1 is Looking forward to hear that and hear it well and B1 is to hear, what should the output be? Looking forward that and hear it well? Or do you want to find to and hear separately?– BruceWayne
Jan 7 at 16:25
Can you clarify with more examples? If
A1 is Looking forward to hear that and hear it well and B1 is to hear, what should the output be? Looking forward that and hear it well? Or do you want to find to and hear separately?– BruceWayne
Jan 7 at 16:25
1
1
Please edit your question to clarify. This may also prove useful for providing a good example.
– cybernetic.nomad
Jan 7 at 16:28
Please edit your question to clarify. This may also prove useful for providing a good example.
– cybernetic.nomad
Jan 7 at 16:28
add a comment |
1 Answer
1
active
oldest
votes
=SUBSTITUTE(A1,A2,"")
Takes the text in A1 and replaces occurrences of the text in A2 with the empty string. So "House of Cards" becomes "House" because "of cards" has been replaced with nothing.
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
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%2f1391542%2fexcel-remove-only-true-cell-content-if-its-in-a-other-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
=SUBSTITUTE(A1,A2,"")
Takes the text in A1 and replaces occurrences of the text in A2 with the empty string. So "House of Cards" becomes "House" because "of cards" has been replaced with nothing.
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
add a comment |
=SUBSTITUTE(A1,A2,"")
Takes the text in A1 and replaces occurrences of the text in A2 with the empty string. So "House of Cards" becomes "House" because "of cards" has been replaced with nothing.
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
add a comment |
=SUBSTITUTE(A1,A2,"")
Takes the text in A1 and replaces occurrences of the text in A2 with the empty string. So "House of Cards" becomes "House" because "of cards" has been replaced with nothing.
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
=SUBSTITUTE(A1,A2,"")
Takes the text in A1 and replaces occurrences of the text in A2 with the empty string. So "House of Cards" becomes "House" because "of cards" has been replaced with nothing.
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
answered Jan 7 at 17:00
Heath RafteryHeath Raftery
28113
28113
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
add a comment |
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
thank you, was exactly what i was looking for!!
– DanTheMan
Jan 8 at 8:56
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%2f1391542%2fexcel-remove-only-true-cell-content-if-its-in-a-other-cell%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
Likely going to need VBA. Must the text be in order? What if
A1isThe theory is the key, and inB1isthe. Do you want bothTheremoved? What have you tried?– BruceWayne
Jan 7 at 16:11
yes...always the individual true value which is next to should be the one which should be removed. if needs to be done via VBA, any idea how?
– DanTheMan
Jan 7 at 16:15
3
Can you clarify with more examples? If
A1isLooking forward to hear that and hear it wellandB1isto hear, what should the output be?Looking forward that and hear it well? Or do you want to findtoandhearseparately?– BruceWayne
Jan 7 at 16:25
1
Please edit your question to clarify. This may also prove useful for providing a good example.
– cybernetic.nomad
Jan 7 at 16:28