Release List Reviews Shop Join News DVD Giveaways Video Games Advertise
DVD Reviews | Theatrical Reviews | Adult DVD Reviews | Video Game Reviews | Price Search Buy Stuff Here
DVD Talk
DVD Reviews DVD Talk Headlines HD Reviews


Add to My Yahoo! - RSS 2.0 - RSS 2.0 - DVD Talk Podcast RSS -


Go Back   DVD Talk Forum > General Discussions > Tech Talk

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Reply
 
Thread Tools
Old 07-11-07, 03:10 PM   #1
thismeansyou
Senior Member
 
Join Date: May 2003
Posts: 264
Excel: how to find and replace everything EXCEPT something?

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!
  Reply With Quote
Old 07-11-07, 03:47 PM   #2
HN
DVD Talk Limited Edition
 
Join Date: Oct 1999
Location: Los Angeles, CA
Posts: 5,581
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'
  Reply With Quote
Old 07-11-07, 05:55 PM   #3
thismeansyou
Senior Member
 
Join Date: May 2003
Posts: 264
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!
  Reply With Quote
Old 07-11-07, 06:06 PM   #4
dtcarson
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 8,229
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.
__________________
I have nothing to say.
My dvds
360 Gamertag: dtcarson
  Reply With Quote
Old 07-12-07, 12:28 AM   #5
thismeansyou
Senior Member
 
Join Date: May 2003
Posts: 264
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
  Reply With Quote
Old 07-12-07, 06:12 AM   #6
dtcarson
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 8,229
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.
__________________
I have nothing to say.
My dvds
360 Gamertag: dtcarson

Last edited by dtcarson; 07-12-07 at 06:14 AM.
  Reply With Quote
Old 07-13-07, 01:54 PM   #7
thismeansyou
Senior Member
 
Join Date: May 2003
Posts: 264
Quote:
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.
  Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


All times are GMT -5. The time now is 09:41 AM.

Rules - DVD Talk - Archive - Privacy Statement - Top

Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.2.0
Copyright 2011 DVDTalk.com All Rights Reserved. Privacy Policy and Terms of Use.