Remove all cell contents except certain words, numbers, symbols

[mlarson]

Hello everybody, hoping you can help me out with this problem. Thanks in advance for taking a look!

I have numerous columns of data. I would like the contents of all cells to be removed except for about 200 specific words (example: green, red, blue), all numbers, blank spaces, and the following three symbols: colon, comma, dash... so : , -

Is there any possible way of doing this with a formula or something other than VBA?


Thanks again!

 

[Peter_SSs]

Is there any possible way of doing this with a formula or something other than VBA?

I doubt it, but to be sure, a small set of dummy sample data and the expected results posted with XL2BB would help readers assess better.

 

[mlarson]

Column A	Column B (output)	Things to keep (not case sensitive)
Sally, I saw 2 dogs.	, 2	Red
Four balloons: green, blue, yellow, red.	: green, , , red	green
Caution: 2 car accident on highway.	: 2	1
		2
		,
		:

 

[mlarson]

Hope that chart helps to clarify what I'm looking for, thanks!

 

[Peter_SSs]

I don't think that would be possible without vba (& may be difficult even with vba).

BTW, what would be the output from this text? (Note that the string "red" is included in the text although the word red is not)
"I saw balloons coloured green and yellow"

 

[mlarson]

In that case coloured would not appear as part of the output. Might be another issue I have to deal with.

Thanks for your help, appreciate it!

 

[Peter_SSs]

In that case coloured would not appear as part of the output.

No, I expected that as "coloured" was not in the list of things to keep. However, "red" was in the list of things to keep so I was checking whether or not to put "red" in the output. I expect also not for that but was just checking.

Speaking of the word "red" what if the text was "The balloon was pink-red in colour"? Here, red is still not a word on its own, but is more so than in coloured.


You said that you did not want vba, but if that is the only option, do you want it considered?