Excel: how to find and replace everything EXCEPT something?

[thismeansyou]

Hey,

I have a project for work in which I'm provided with an excel file with hundreds of rows of text. I need to be able to identify every cell with a specific word. What I want to do is duplicate the column, replace the word with something crazy like %%%%%, and then 'Find and Replace' everything except the %%%%%, leaving me with an ID of which row had that word. I can then replace the %%%%% with a "1" for future statistical analysis.

I can't seem to find any way to replace everything except a given sequence, so I've taken to replacing a,b,c,d,etc... with emptiness, including numbers and characters and all. This is super-tedious, though.

If excel can't do it, is there any other free/cheap program that could?

Thanks!

[HN]

I don't know about Excel, but I think you can do what you want in Word:

In this example, I will use the word 'crazy' as the word you want to keep.

1. copy and paste your column from Excel to Word
2. In Word, open the Find box (ctrl F)
3. Check the "Highlight all items found in:" box
4. Check the "Use wildcards box"
5. in the "Find what:" field, put in "*[!crazy]" (do not include the quote marks)
6. Click "Find All"

RESULT: what you'll see in the document now is everything is highlighted except the word 'crazy'

[thismeansyou]

Ohhh man, that helped a lot. It works, and it's gonna save me a bunch of time! I didn't think to try Word because I figured Excel would have an equally, if not more robust Find and Replace than Word. Apparently not.

THANKS!

[dtcarson]

If all you need is
"an ID of which row had that word"
why not just add a column and do something like the following (searching for the string "bo", and B1 being the first cell with data in my example)

IF(ISERROR((SEARCH("bo",B1))),"No","Yes")

You could even replace No and Yes with 0 and 1 and build a pivottable showing the total.
Of course carry that formula down so it applies to each cell with data.

[thismeansyou]

Dang, that's gonna save me even more time and make my search even more flexible! Thanks a lot to both of you, I was going crazy with my convoluted method...

dtcarson, where do you learn to do stuff like that? I looked in help and it wasn't very helpful. Is there a good book I can get to teach me how to get the most out of Excel?

S

[dtcarson]

I agree, Excel's help files are not very helpful.
I just play around with it, trying to use the different functions. I use excel probably 4 hours a day or more at work, and have for quite a while, so I learn something new or a new way of doing something very often. Excel will do most things we want it to, you just have to figure out how to tell it what we want. I'll open the functions list and just go try them out. You can stack/combine most functions, the important thing there is not to confuse yourself when combining them.
It's old, but I have John Walkenbach's "Excel 2000 Bible", it is a relatively easy read and is a pretty good reference book. I've even asked about some more complex things on boards or usenet (I've still got the printout for when I asked about using dynamic named ranges to create dynamically updating charts on Usenet three years ago, that's something I rely on, but don't use enough to "know" how to do.)

The only concern with my solution is that it works best if you have your text laid out regularly (all in one column, for example) so it basically looks in one cell per row. If your text is irregular it could be a little trickier.

[thismeansyou]

Originally Posted by dtcarson

I agree, Excel's help files are not very helpful.
I just play around with it, trying to use the different functions. I use excel probably 4 hours a day or more at work, and have for quite a while, so I learn something new or a new way of doing something very often. Excel will do most things we want it to, you just have to figure out how to tell it what we want. I'll open the functions list and just go try them out. You can stack/combine most functions, the important thing there is not to confuse yourself when combining them.
It's old, but I have John Walkenbach's "Excel 2000 Bible", it is a relatively easy read and is a pretty good reference book. I've even asked about some more complex things on boards or usenet (I've still got the printout for when I asked about using dynamic named ranges to create dynamically updating charts on Usenet three years ago, that's something I rely on, but don't use enough to "know" how to do.)

The only concern with my solution is that it works best if you have your text laid out regularly (all in one column, for example) so it basically looks in one cell per row. If your text is irregular it could be a little trickier.

Thanks.. Your method is perfect for what I have; each patient's data is in one cell. If i come across some more complex stuff I'll try messing with them functions.