Remove all cell contents except certain words, numbers, symbols

mlarson

Active Member
Joined
Aug 25, 2011
Messages
477
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,424
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 25, 2011
Messages
477
Office Version
  1. 2010
Platform
  1. Windows
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
,
:
 

mlarson

Active Member
Joined
Aug 25, 2011
Messages
477
Office Version
  1. 2010
Platform
  1. Windows
Hope that chart helps to clarify what I'm looking for, thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,424
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 25, 2011
Messages
477
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,424
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,181,695
Messages
5,931,498
Members
436,790
Latest member
aelbaitam

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
Top