How to delete composed keywords separated by comma into a cell?

**lucato** · 02-04-2012, 09:03 AM

Hi folks,

Lets suppose I have the cell A1 with 100 words or more separated by comma, but the words can be composed word or double words such as "jump up", "Old age","sunny day", or more "holding a face", but I want to find these composed words or double/triple+ words with space that are between commas, and delete them and just leave the single words. So, as example let's suppose my cell A1 was as:

[A1] word1, jump up, word3, word4, Old age, word6, Sunny day, holding a face,...word100

Want the result as

[A1] word1, word3, word4, word6, ...word100

Is there some way to create a formula on B1 to get the result above or is it possible to put them within double-quotes as shown below:

[A1] word1, "jump up", word3, word4, "Old age", word6, "Sunny day", "holding a face",...word100

Thanks in advance.

**Fotis1991** · 02-04-2012, 09:06 AM

Hi lucato.

Is the same question with this??...

http://www.excelforum.com/excel-gene...to-a-cell.html

**lucato** · 02-04-2012, 09:18 AM

No, it is different from that post subject, but has the same question in the thread to member that was helping me with the other issue. So, how it isn't the subject of that title was created a new one.

**Pete_UK** · 02-04-2012, 09:44 AM

You can do it directly on the cell by applying Find & Replace. First put a <comma><space> before the first word, and a <comma><space> after the final word. Highlight cells A1 and A2 (so that this will not affect other cells), then CTRL-H, and then in the dialogue box:

Find what: <comma><space><asterisk><space><asterisk><comma><space>, i.e. ", * *, " without the quotes
Replace with: <comma><space>, i.e. ", " without the quotes
Click Replace all

this will remove all words containing one or more spaces within the text. You can then delete the <comma><space> before the first word and after the last word.

Hope this helps.

Pete

**lucato** · 02-04-2012, 09:53 AM

Hey Pete great trick as non-formula, thanks for your tip.

It is a pity, that the Replace with doesn't accept "*" as wildcard character.

**lucato** · 02-04-2012, 03:50 PM

Hi Pete, just one more question. Your trick doesn't work for the latest word on the righ. I mean, If I have on the cell:

[A1] word1, word2, word3, word4, double word

and if I...
Find what: <comma><space><asterisk><space><asterisk><comma>, i.e. ", * *" without the quotes
Replace with: <nothing>, i.e. "" without the quotes

The result will be:
[A1] word1

Instead of [A1] word1, word2, word3, word4

Any idea how to trick the latest on the very right?

Thanks.

**lucato** · 02-04-2012, 03:58 PM

Btw, I edited the 1st post, instead of deleting, is there some way to create a formula on B1 or find/replace to put them within double-quotes as shown below:

[A1] word1, "jump up", word3, word4, "Old age", word6, "Sunny day", "holding a face",...word100

Thanks.

**Pete_UK** · 02-04-2012, 04:43 PM

Originally Posted by lucato

Hi Pete, just one more question. Your trick doesn't work for the latest word on the righ. I mean, If I have on the cell:

[A1] word1, word2, word3, word4, double word

and if I...
Find what: <comma><space><asterisk><space><asterisk><comma>, i.e. ", * *" without the quotes
Replace with: <nothing>, i.e. "" without the quotes

The result will be:
[A1] word1

Instead of [A1] word1, word2, word3, word4

Any idea how to trick the latest on the very right?

Thanks.

Yes - read my post again. I said to put comma-space before the first word and after the last word so that the operation only needs to be carried out once, then remove those after you have done it.

Hope this helps.

Pete

**Pete_UK** · 02-04-2012, 04:50 PM

Originally Posted by lucato

Btw, I edited the 1st post, ....

You should not edit a post that has been answered - it will make no sense to anyone reading it after you have changed it.

The answer to your question is - No. The asterisk (wildcard) can be used to search for any characters, but it can't be used to substitute the same characters back into the text.

Hope this helps.

Pete

**lucato** · 02-04-2012, 11:48 PM

Originally Posted by Pete_UK

Yes - read my post again. I said to put comma-space before the first word and after the last word so that the operation only needs to be carried out once, then remove those after you have done it.

Hope this helps.

Pete

Ops, my fault, that's true, I forgot to add them in the beggining and end, I will try again.

Thanks.

**lucato** · 02-04-2012, 11:56 PM

Originally Posted by Pete_UK

You should not edit a post that has been answered - it will make no sense to anyone reading it after you have changed it.

The answer to your question is - No. The asterisk (wildcard) can be used to search for any characters, but it can't be used to substitute the same characters back into the text.

Hope this helps.

Pete

I'm sorry but I don't agree that won't make sense once it was just one insertion of extra doubt. It won't reflect in the other replyes.
Regarding the "No" are you talking about the asterisk or NO that isn't possible to put them within double-quotes as shown below:
[A1] word1, "jump up", word3, word4, "Old age", word6, "Sunny day", "holding a face",...word100

Thanks and have a nice Sunday