Excel - Bulk deletion of same words and rest of differing brackets content

up vote
0
down vote

favorite

I am compiling a glossary in excel, with entries coming from different sources. In the attempt to make it more uniform, I intend to delete the content of the "definition" rows indicating the field of usage of certain definitions.

My aim is to delete at once

(wordA wordB) 

(wordA wordC) 

(wordA wordD wordE)

from

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

in all rows, deleting also the space after the bracket but leaving

text1

text2

text3

untouched. Thank you in advance for any contribution.
PS: my office version is 2010, but can get access to a PC with the latest version if required!

ADDITION
Thank you very much for your contributions, but I forgot to add that some definitions have brackets containing useful information

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

(wordA wordF) text4 (useful info between brackets) text

Hence I need to be able to retain everything contained in the cell, except for the content of brackets containing "wordA"

edited Nov 10 at 15:09

asked Nov 10 at 14:05

ed0

1034

1

Are the definition in individual cells?
– Wizhi
Nov 10 at 14:08

1

do a find/replace ie find (*) and replace with ""
– Solar Mike
Nov 10 at 14:09

@Whizhi Yes, they are in individual cells
– ed0
Nov 10 at 14:37

add a comment |

up vote
0
down vote

favorite

My aim is to delete at once

(wordA wordB) 

(wordA wordC) 

(wordA wordD wordE)

from

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

in all rows, deleting also the space after the bracket but leaving

text1

text2

text3

untouched. Thank you in advance for any contribution.
PS: my office version is 2010, but can get access to a PC with the latest version if required!

ADDITION
Thank you very much for your contributions, but I forgot to add that some definitions have brackets containing useful information

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

(wordA wordF) text4 (useful info between brackets) text

Hence I need to be able to retain everything contained in the cell, except for the content of brackets containing "wordA"

edited Nov 10 at 15:09

asked Nov 10 at 14:05

ed0

1034

1

Are the definition in individual cells?
– Wizhi
Nov 10 at 14:08

1

do a find/replace ie find (*) and replace with ""
– Solar Mike
Nov 10 at 14:09

@Whizhi Yes, they are in individual cells
– ed0
Nov 10 at 14:37

add a comment |

up vote
0
down vote

favorite

My aim is to delete at once

(wordA wordB) 

(wordA wordC) 

(wordA wordD wordE)

from

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

in all rows, deleting also the space after the bracket but leaving

text1

text2

text3

untouched. Thank you in advance for any contribution.
PS: my office version is 2010, but can get access to a PC with the latest version if required!

ADDITION
Thank you very much for your contributions, but I forgot to add that some definitions have brackets containing useful information

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

(wordA wordF) text4 (useful info between brackets) text

Hence I need to be able to retain everything contained in the cell, except for the content of brackets containing "wordA"

edited Nov 10 at 15:09

asked Nov 10 at 14:05

ed0

1034

My aim is to delete at once

(wordA wordB) 

(wordA wordC) 

(wordA wordD wordE)

from

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

in all rows, deleting also the space after the bracket but leaving

text1

text2

text3

untouched. Thank you in advance for any contribution.
PS: my office version is 2010, but can get access to a PC with the latest version if required!

ADDITION
Thank you very much for your contributions, but I forgot to add that some definitions have brackets containing useful information

(wordA wordB) text1

(wordA wordC) text2

(wordA wordD wordE) text3

(wordA wordF) text4 (useful info between brackets) text

Hence I need to be able to retain everything contained in the cell, except for the content of brackets containing "wordA"

excel vba ms-office bulk office-2010

edited Nov 10 at 15:09

asked Nov 10 at 14:05

ed0

1034

edited Nov 10 at 15:09

asked Nov 10 at 14:05

ed0

1034

edited Nov 10 at 15:09

asked Nov 10 at 14:05

ed0

1034

asked Nov 10 at 14:05

ed0

1034

asked Nov 10 at 14:05

ed0

1034

1

Are the definition in individual cells?
– Wizhi
Nov 10 at 14:08

1

do a find/replace ie find (*) and replace with ""
– Solar Mike
Nov 10 at 14:09

@Whizhi Yes, they are in individual cells
– ed0
Nov 10 at 14:37

add a comment |

1

Are the definition in individual cells?
– Wizhi
Nov 10 at 14:08

1

do a find/replace ie find (*) and replace with ""
– Solar Mike
Nov 10 at 14:09

@Whizhi Yes, they are in individual cells
– ed0
Nov 10 at 14:37

Are the definition in individual cells?
– Wizhi
Nov 10 at 14:08

do a find/replace ie find (*) and replace with ""
– Solar Mike
Nov 10 at 14:09

@Whizhi Yes, they are in individual cells
– ed0
Nov 10 at 14:37

add a comment |

2 Answers
2

active

oldest

votes

up vote
3
down vote

accepted

If your data looks like this:

enter image description here

You can use:

=RIGHT(A1,LEN(A1)-FIND(") ",A1))

to remove all the words before ") "

I just have to share @Solar Mikes solution in the comment because it's amazing!!:

In "Find and Replace" you enter in the field Find what: "(*)" (with a space after the last bracket)

Because it will find anything in the bracket [* = wildcard] and replace that.

And replace with "nothing" aka "".

enter image description here

Output will be this:

enter image description here

edited Nov 10 at 14:19

answered Nov 10 at 14:10

Wizhi

3,3261730

2

before not after, but will work.
– Solar Mike
Nov 10 at 14:11

1

Wouldn't the first solution work? example
– Wizhi
Nov 10 at 14:50

1

Then use find replace with the terms you do want to delete, not explaining clearly makes it hard for us to help.
– Solar Mike
Nov 10 at 15:05

1

The FIND value is going to stop at the first bracket. In your case that's what you want. So.. LEN shows us how many characters there are in the cell, and FIND function will find the position of the FIRST bracket from left to return. (LEN(A1) - FIND()), will be how many characters from the right the RIGHT function should return. Examples and details can be found here :)
– Wizhi
Nov 10 at 15:18

1

My apologies Solar Mike, I appreciate your intention to help me, and hope you'll accept my apologies for my lack of clarity. Didn't mean to make you waste time and effort, and least of all upset you. Thanks for your help as well!
– ed0
Nov 10 at 15:25

|
show 8 more comments

up vote
1
down vote

You can use "Text to Column" function of excel. Your data will be segregated exactly the way you need in different columns then just delete the column you don't need.

Correct me if I am wrong..

answered Nov 10 at 17:29

Rashmi Veena

235

That's well thought of, but I think that since I have different amounts of words in different brackets, some of the in-brackets content would end up in the same column as out-of-brackets text from cells below. Correct me if I'm wrong :)
– ed0
Nov 10 at 18:08

1

Yes It might happen. My answer was based on the pattern you shared. You can check this once by copying your data in a different sheet. If it works, we will have one more way of solving the problem. I am new to excel so just exploring things..
– Rashmi Veena
Nov 11 at 11:21

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53239752%2fexcel-bulk-deletion-of-same-words-and-rest-of-differing-brackets-content%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

up vote
3
down vote

accepted

If your data looks like this:

enter image description here

You can use:

=RIGHT(A1,LEN(A1)-FIND(") ",A1))

to remove all the words before ") "

I just have to share @Solar Mikes solution in the comment because it's amazing!!:

In "Find and Replace" you enter in the field Find what: "(*)" (with a space after the last bracket)

Because it will find anything in the bracket [* = wildcard] and replace that.

And replace with "nothing" aka "".

enter image description here

Output will be this:

enter image description here

edited Nov 10 at 14:19

answered Nov 10 at 14:10

Wizhi

3,3261730

2

before not after, but will work.
– Solar Mike
Nov 10 at 14:11

1

Wouldn't the first solution work? example
– Wizhi
Nov 10 at 14:50

1

Then use find replace with the terms you do want to delete, not explaining clearly makes it hard for us to help.
– Solar Mike
Nov 10 at 15:05

1

The FIND value is going to stop at the first bracket. In your case that's what you want. So.. LEN shows us how many characters there are in the cell, and FIND function will find the position of the FIRST bracket from left to return. (LEN(A1) - FIND()), will be how many characters from the right the RIGHT function should return. Examples and details can be found here :)
– Wizhi
Nov 10 at 15:18

1

My apologies Solar Mike, I appreciate your intention to help me, and hope you'll accept my apologies for my lack of clarity. Didn't mean to make you waste time and effort, and least of all upset you. Thanks for your help as well!
– ed0
Nov 10 at 15:25

|
show 8 more comments

up vote
3
down vote

accepted

If your data looks like this:

enter image description here

You can use:

=RIGHT(A1,LEN(A1)-FIND(") ",A1))

to remove all the words before ") "

I just have to share @Solar Mikes solution in the comment because it's amazing!!:

In "Find and Replace" you enter in the field Find what: "(*)" (with a space after the last bracket)

Because it will find anything in the bracket [* = wildcard] and replace that.

And replace with "nothing" aka "".

enter image description here

Output will be this:

enter image description here

edited Nov 10 at 14:19

answered Nov 10 at 14:10

Wizhi

3,3261730

2

before not after, but will work.
– Solar Mike
Nov 10 at 14:11

1

Wouldn't the first solution work? example
– Wizhi
Nov 10 at 14:50

1

Then use find replace with the terms you do want to delete, not explaining clearly makes it hard for us to help.
– Solar Mike
Nov 10 at 15:05

1

The FIND value is going to stop at the first bracket. In your case that's what you want. So.. LEN shows us how many characters there are in the cell, and FIND function will find the position of the FIRST bracket from left to return. (LEN(A1) - FIND()), will be how many characters from the right the RIGHT function should return. Examples and details can be found here :)
– Wizhi
Nov 10 at 15:18

1

My apologies Solar Mike, I appreciate your intention to help me, and hope you'll accept my apologies for my lack of clarity. Didn't mean to make you waste time and effort, and least of all upset you. Thanks for your help as well!
– ed0
Nov 10 at 15:25

|
show 8 more comments

up vote
3
down vote

accepted

If your data looks like this:

enter image description here

You can use:

=RIGHT(A1,LEN(A1)-FIND(") ",A1))

to remove all the words before ") "

I just have to share @Solar Mikes solution in the comment because it's amazing!!:

In "Find and Replace" you enter in the field Find what: "(*)" (with a space after the last bracket)

Because it will find anything in the bracket [* = wildcard] and replace that.

And replace with "nothing" aka "".

enter image description here

Output will be this:

enter image description here

edited Nov 10 at 14:19

answered Nov 10 at 14:10

Wizhi

3,3261730

If your data looks like this:

enter image description here

You can use:

=RIGHT(A1,LEN(A1)-FIND(") ",A1))

to remove all the words before ") "

I just have to share @Solar Mikes solution in the comment because it's amazing!!:

In "Find and Replace" you enter in the field Find what: "(*)" (with a space after the last bracket)

Because it will find anything in the bracket [* = wildcard] and replace that.

And replace with "nothing" aka "".

enter image description here

Output will be this:

enter image description here

edited Nov 10 at 14:19

answered Nov 10 at 14:10

Wizhi

3,3261730

edited Nov 10 at 14:19

answered Nov 10 at 14:10

Wizhi

3,3261730

answered Nov 10 at 14:10

Wizhi

3,3261730

answered Nov 10 at 14:10

Wizhi

3,3261730

2

before not after, but will work.
– Solar Mike
Nov 10 at 14:11

1

Wouldn't the first solution work? example
– Wizhi
Nov 10 at 14:50

1

Then use find replace with the terms you do want to delete, not explaining clearly makes it hard for us to help.
– Solar Mike
Nov 10 at 15:05

1

The FIND value is going to stop at the first bracket. In your case that's what you want. So.. LEN shows us how many characters there are in the cell, and FIND function will find the position of the FIRST bracket from left to return. (LEN(A1) - FIND()), will be how many characters from the right the RIGHT function should return. Examples and details can be found here :)
– Wizhi
Nov 10 at 15:18

1

My apologies Solar Mike, I appreciate your intention to help me, and hope you'll accept my apologies for my lack of clarity. Didn't mean to make you waste time and effort, and least of all upset you. Thanks for your help as well!
– ed0
Nov 10 at 15:25

|
show 8 more comments

2

before not after, but will work.
– Solar Mike
Nov 10 at 14:11

1

Wouldn't the first solution work? example
– Wizhi
Nov 10 at 14:50

1

Then use find replace with the terms you do want to delete, not explaining clearly makes it hard for us to help.
– Solar Mike
Nov 10 at 15:05

1

The FIND value is going to stop at the first bracket. In your case that's what you want. So.. LEN shows us how many characters there are in the cell, and FIND function will find the position of the FIRST bracket from left to return. (LEN(A1) - FIND()), will be how many characters from the right the RIGHT function should return. Examples and details can be found here :)
– Wizhi
Nov 10 at 15:18

1

My apologies Solar Mike, I appreciate your intention to help me, and hope you'll accept my apologies for my lack of clarity. Didn't mean to make you waste time and effort, and least of all upset you. Thanks for your help as well!
– ed0
Nov 10 at 15:25

before not after, but will work.
– Solar Mike
Nov 10 at 14:11

Wouldn't the first solution work? example
– Wizhi
Nov 10 at 14:50

Then use find replace with the terms you do want to delete, not explaining clearly makes it hard for us to help.
– Solar Mike
Nov 10 at 15:05

The FIND value is going to stop at the first bracket. In your case that's what you want. So.. LEN shows us how many characters there are in the cell, and FIND function will find the position of the FIRST bracket from left to return. (LEN(A1) - FIND()), will be how many characters from the right the RIGHT function should return. Examples and details can be found here :)
– Wizhi
Nov 10 at 15:18

My apologies Solar Mike, I appreciate your intention to help me, and hope you'll accept my apologies for my lack of clarity. Didn't mean to make you waste time and effort, and least of all upset you. Thanks for your help as well!
– ed0
Nov 10 at 15:25

|
show 8 more comments

up vote
1
down vote

You can use "Text to Column" function of excel. Your data will be segregated exactly the way you need in different columns then just delete the column you don't need.

Correct me if I am wrong..

answered Nov 10 at 17:29

Rashmi Veena

235

That's well thought of, but I think that since I have different amounts of words in different brackets, some of the in-brackets content would end up in the same column as out-of-brackets text from cells below. Correct me if I'm wrong :)
– ed0
Nov 10 at 18:08

1

Yes It might happen. My answer was based on the pattern you shared. You can check this once by copying your data in a different sheet. If it works, we will have one more way of solving the problem. I am new to excel so just exploring things..
– Rashmi Veena
Nov 11 at 11:21

add a comment |

up vote
1
down vote

You can use "Text to Column" function of excel. Your data will be segregated exactly the way you need in different columns then just delete the column you don't need.

Correct me if I am wrong..

answered Nov 10 at 17:29

Rashmi Veena

235

That's well thought of, but I think that since I have different amounts of words in different brackets, some of the in-brackets content would end up in the same column as out-of-brackets text from cells below. Correct me if I'm wrong :)
– ed0
Nov 10 at 18:08

1

Yes It might happen. My answer was based on the pattern you shared. You can check this once by copying your data in a different sheet. If it works, we will have one more way of solving the problem. I am new to excel so just exploring things..
– Rashmi Veena
Nov 11 at 11:21

add a comment |

up vote
1
down vote

You can use "Text to Column" function of excel. Your data will be segregated exactly the way you need in different columns then just delete the column you don't need.

Correct me if I am wrong..

answered Nov 10 at 17:29

Rashmi Veena

235

You can use "Text to Column" function of excel. Your data will be segregated exactly the way you need in different columns then just delete the column you don't need.

Correct me if I am wrong..

answered Nov 10 at 17:29

Rashmi Veena

235

answered Nov 10 at 17:29

Rashmi Veena

235

answered Nov 10 at 17:29

Rashmi Veena

235

answered Nov 10 at 17:29

Rashmi Veena

235

That's well thought of, but I think that since I have different amounts of words in different brackets, some of the in-brackets content would end up in the same column as out-of-brackets text from cells below. Correct me if I'm wrong :)
– ed0
Nov 10 at 18:08

1

Yes It might happen. My answer was based on the pattern you shared. You can check this once by copying your data in a different sheet. If it works, we will have one more way of solving the problem. I am new to excel so just exploring things..
– Rashmi Veena
Nov 11 at 11:21

add a comment |

That's well thought of, but I think that since I have different amounts of words in different brackets, some of the in-brackets content would end up in the same column as out-of-brackets text from cells below. Correct me if I'm wrong :)
– ed0
Nov 10 at 18:08

1

Yes It might happen. My answer was based on the pattern you shared. You can check this once by copying your data in a different sheet. If it works, we will have one more way of solving the problem. I am new to excel so just exploring things..
– Rashmi Veena
Nov 11 at 11:21

That's well thought of, but I think that since I have different amounts of words in different brackets, some of the in-brackets content would end up in the same column as out-of-brackets text from cells below. Correct me if I'm wrong :)
– ed0
Nov 10 at 18:08

Yes It might happen. My answer was based on the pattern you shared. You can check this once by copying your data in a different sheet. If it works, we will have one more way of solving the problem. I am new to excel so just exploring things..
– Rashmi Veena
Nov 11 at 11:21

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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

搜尋此網誌

Wsrtjtyk