Remove all cell contents except certain words, numbers, symbols

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
Column AColumn B (output)Things to keep (not case sensitive)
Sally, I saw 2 dogs., 2Red
Four balloons: green, blue, yellow, red.: green, , , redgreen
Caution: 2 car accident on highway.: 21
2
,
:
 
Upvote 0
Hope that chart helps to clarify what I'm looking for, thanks!
 
Upvote 0
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"
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top