Cycling and bicycle discussion forums. 
   Click here to join our community Log in to access your Control Panel  


Go Back   > >

Foo Off-Topic chit chat with no general subject.

User Tag List

Reply
 
Thread Tools Search this Thread
Old 06-28-10, 09:50 AM   #1
phantomcow2
la vache fantôme
Thread Starter
 
phantomcow2's Avatar
 
Join Date: Aug 2004
Location: NH
Bikes:
Posts: 6,266
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
SEARCH function in Excel.

I'm trying to use the search function to search for two strings in text. That is, I have a spreadsheet whose "L" column is populated with short phrases, like "I ate a rotten orange and mango today." It could be anything. The point is I want to use the SEARCH function to find entries that mention mango OR orange. I thought I'd use something like
=SEARCH(OR("mango", "orange")$L$1:$L$250) but it isn't working. How can I adapt the SEARCH function to search for multiple strings?
__________________
C://dos
C://dos.run
run.dos.run
phantomcow2 is offline   Reply With Quote
Old 06-28-10, 10:36 AM   #2
jccaclimber
Senior Member
 
Join Date: May 2005
Location: Terre Haute, Lafayette, or Indianapolis, IN, depending on the day
Bikes: n, I would like n+1
Posts: 1,917
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
I'm sure there's a better way, but you could search for 'mango', then 'orange', and sum the results. What is your desired output format?
jccaclimber is offline   Reply With Quote
Old 06-28-10, 10:37 AM   #3
ModoVincere
Riding Heaven's Highways on the grand tour
 
ModoVincere's Avatar
 
Join Date: Aug 2006
Bikes:
Posts: 1,675
Mentioned: 1 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Try the = Find(find_text,within_text,start_num) function.
__________________
1 bronze, 0 silver, 1 gold
ModoVincere is offline   Reply With Quote
Old 06-28-10, 10:37 AM   #4
TexasGuy
That darn Yankee
 
TexasGuy's Avatar
 
Join Date: Jun 2005
Location: West West Fort Worth
Bikes: Mongoose XR-100, Eros Bianchi
Posts: 4,286
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
I don't think the search function is designed to search a range. I think this is the job of a macro.
__________________
Life is about hanging onto what you think is important and finding out what really is important.
"Stop Ruining my joke!", "No, a joke implies humor attached at no additional cost"
So many sayings, so little sig space.
TexasGuy is offline   Reply With Quote
Old 06-28-10, 10:38 AM   #5
jsharr
You Know!? For Kids!
 
jsharr's Avatar
 
Join Date: Apr 2005
Location: Just NW of Richardson Bike Mart
Bikes: '05 Trek 1200 / '90 Trek 8000 / '? Falcon Europa
Posts: 6,157
Mentioned: 2 Post(s)
Tagged: 0 Thread(s)
Quoted: 3 Post(s)
I would just shove a fruit bat into the CD rom drive.
__________________
Are you a registered member? Why not? Click here to register. It's free and only takes 27 seconds! Help out the forums, abide by our community guidelines.
Quote:
Originally Posted by colorider View Post
Phobias are for irrational fears. Fear of junk ripping badgers is perfectly rational. Those things are nasty.
jsharr is offline   Reply With Quote
Old 06-28-10, 10:39 AM   #6
TexasGuy
That darn Yankee
 
TexasGuy's Avatar
 
Join Date: Jun 2005
Location: West West Fort Worth
Bikes: Mongoose XR-100, Eros Bianchi
Posts: 4,286
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by ModoVincere View Post
Try the = Find(find_text,within_text,start_num) function.
Find is has the same declaration as Search pretty much.
I don't believe that within_text is allowed to be a range.
It's supposed to be either a string value or a single cellreference. the 3rd parameter is the indexto start looking for the pattern after.
__________________
Life is about hanging onto what you think is important and finding out what really is important.
"Stop Ruining my joke!", "No, a joke implies humor attached at no additional cost"
So many sayings, so little sig space.
TexasGuy is offline   Reply With Quote
Old 06-28-10, 10:42 AM   #7
phantomcow2
la vache fantôme
Thread Starter
 
phantomcow2's Avatar
 
Join Date: Aug 2004
Location: NH
Bikes:
Posts: 6,266
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Find is the same thing as Search except that it's case sensitive. My desired output is to sort through the ~250 entires with mango or orange mentioned and produce a new column indicating where this occurs.
__________________
C://dos
C://dos.run
run.dos.run
phantomcow2 is offline   Reply With Quote
Old 06-28-10, 10:42 AM   #8
TexasGuy
That darn Yankee
 
TexasGuy's Avatar
 
Join Date: Jun 2005
Location: West West Fort Worth
Bikes: Mongoose XR-100, Eros Bianchi
Posts: 4,286
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
http://support.microsoft.com/kb/282151
The macro Code in the 2nd box in (Workaround) section is similar to what you want.

this person wrote a FindAll VBA macro/function that is reusable.
http://www.cpearson.com/excel/FindAll.aspx
__________________
Life is about hanging onto what you think is important and finding out what really is important.
"Stop Ruining my joke!", "No, a joke implies humor attached at no additional cost"
So many sayings, so little sig space.
TexasGuy is offline   Reply With Quote
Old 06-28-10, 10:51 AM   #9
ModoVincere
Riding Heaven's Highways on the grand tour
 
ModoVincere's Avatar
 
Join Date: Aug 2006
Bikes:
Posts: 1,675
Mentioned: 1 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by TexasGuy View Post
Find is has the same declaration as Search pretty much.
I don't believe that within_text is allowed to be a range.
It's supposed to be either a string value or a single cellreference. the 3rd parameter is the indexto start looking for the pattern after.
this is correct. An easy way around this is to place the string to be looked for at the top of the column, and use absolute references or semi absolute references in order to search for multiple items. Filtering for results or even using a pivot table to summarize results.

Of course, if you know access and VBA, one can simply load sql results into a result set and then search those result sets.
__________________
1 bronze, 0 silver, 1 gold
ModoVincere is offline   Reply With Quote
Old 06-28-10, 11:06 AM   #10
apclassic9
Caustic Soccer Mom
 
apclassic9's Avatar
 
Join Date: Jun 2005
Location: Millstone WV
Bikes:
Posts: 1,760
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
try a combination formula like =Search("mango")AND("orange") - using whatever syntax excel uses. I use Lotus 123, where I would use a complex "IF" statement in a new column. That might work in excel.
__________________
As with mud, life, too, slides by.
apclassic9 is offline   Reply With Quote
Old 06-28-10, 11:11 AM   #11
bigbenaugust 
always rides with luggage
 
bigbenaugust's Avatar
 
Join Date: Feb 2005
Location: KIGX
Bikes: 2009 Fantom CX, 2012 Fantom Cross Uno, Bakfiets
Posts: 1,984
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 11 Post(s)
Quote:
Originally Posted by jsharr View Post
I would just shove a fruit bat into the CD rom drive.
Kind of messy, don't you think? I'd open the case and put it in that way.
__________________
--Ben
Carrboro Bike Coalition - putting the "bike" in "CARrboro" :)
2011 Motobecane Fantom Cross Uno, 2009 Motobecane Fantom CX, and a Bakfiets
Previously: 2000 Trek 4500 (2000-2003), 2003 Novara Randonee (2003-2006), 2003 Giant Rainier (2003-2008), 2005 Xootr Swift (2005-2007), 2007 Nashbar 1x9 (2007-2011), 2011 Windsor Shetland (2011-2014), 2008 Citizen Folder (2015)
Non-Bike hardware: openSUSE Leap - LinuxMint Debian Edition 2 - Mac OS 10.6 - Android 4.4 - CyanogenMod 13
bigbenaugust is offline   Reply With Quote
Old 06-28-10, 11:16 AM   #12
jsharr
You Know!? For Kids!
 
jsharr's Avatar
 
Join Date: Apr 2005
Location: Just NW of Richardson Bike Mart
Bikes: '05 Trek 1200 / '90 Trek 8000 / '? Falcon Europa
Posts: 6,157
Mentioned: 2 Post(s)
Tagged: 0 Thread(s)
Quoted: 3 Post(s)
Quote:
Originally Posted by bigbenaugust View Post
Kind of messy, don't you think? I'd open the case and put it in that way.
I should have prefaced my post with the fact that I am not a techno geek. You can open the case? Really?
__________________
Are you a registered member? Why not? Click here to register. It's free and only takes 27 seconds! Help out the forums, abide by our community guidelines.
Quote:
Originally Posted by colorider View Post
Phobias are for irrational fears. Fear of junk ripping badgers is perfectly rational. Those things are nasty.
jsharr is offline   Reply With Quote
Old 06-28-10, 11:19 AM   #13
ModoVincere
Riding Heaven's Highways on the grand tour
 
ModoVincere's Avatar
 
Join Date: Aug 2006
Bikes:
Posts: 1,675
Mentioned: 1 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by bigbenaugust View Post
Kind of messy, don't you think? I'd open the case and put it in that way.
Quote:
Originally Posted by jsharr View Post
I should have prefaced my post with the fact that I am not a techno geek. You can open the case? Really?

So, if its an apple, you're making fruit salad? where do the raisins go?
__________________
1 bronze, 0 silver, 1 gold
ModoVincere is offline   Reply With Quote
Old 06-28-10, 11:32 AM   #14
jsharr
You Know!? For Kids!
 
jsharr's Avatar
 
Join Date: Apr 2005
Location: Just NW of Richardson Bike Mart
Bikes: '05 Trek 1200 / '90 Trek 8000 / '? Falcon Europa
Posts: 6,157
Mentioned: 2 Post(s)
Tagged: 0 Thread(s)
Quoted: 3 Post(s)
You can run Excel on an apple? ON me, the raisins go straight through, so I am going to answer "toilet".
__________________
Are you a registered member? Why not? Click here to register. It's free and only takes 27 seconds! Help out the forums, abide by our community guidelines.
Quote:
Originally Posted by colorider View Post
Phobias are for irrational fears. Fear of junk ripping badgers is perfectly rational. Those things are nasty.
jsharr is offline   Reply With Quote
Old 06-28-10, 11:37 AM   #15
ModoVincere
Riding Heaven's Highways on the grand tour
 
ModoVincere's Avatar
 
Join Date: Aug 2006
Bikes:
Posts: 1,675
Mentioned: 1 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Toilet, apple.....same thing.
__________________
1 bronze, 0 silver, 1 gold
ModoVincere is offline   Reply With Quote
Old 06-28-10, 11:59 AM   #16
bigbenaugust 
always rides with luggage
 
bigbenaugust's Avatar
 
Join Date: Feb 2005
Location: KIGX
Bikes: 2009 Fantom CX, 2012 Fantom Cross Uno, Bakfiets
Posts: 1,984
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 11 Post(s)
Quote:
Originally Posted by jsharr View Post
I should have prefaced my post with the fact that I am not a techno geek. You can open the case? Really?
Yes, but do it carefully so as not to interrupt the gnomes working in there. Also, don't disconnect the tubes if you like Internet access.

But the real question is: Why is he looking for oranges and mangoes in his spreadsheet? They're at the store (or on a tree)!
__________________
--Ben
Carrboro Bike Coalition - putting the "bike" in "CARrboro" :)
2011 Motobecane Fantom Cross Uno, 2009 Motobecane Fantom CX, and a Bakfiets
Previously: 2000 Trek 4500 (2000-2003), 2003 Novara Randonee (2003-2006), 2003 Giant Rainier (2003-2008), 2005 Xootr Swift (2005-2007), 2007 Nashbar 1x9 (2007-2011), 2011 Windsor Shetland (2011-2014), 2008 Citizen Folder (2015)
Non-Bike hardware: openSUSE Leap - LinuxMint Debian Edition 2 - Mac OS 10.6 - Android 4.4 - CyanogenMod 13
bigbenaugust is offline   Reply With Quote
Old 06-28-10, 12:03 PM   #17
Tude 
Blasted Weeds
 
Tude's Avatar
 
Join Date: Aug 2006
Location: Rochester, NY
Bikes: Trek 1200C, Specialized Rockhopper, Giant Yukon FX, Giant Acapulco
Posts: 1,182
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by apclassic9 View Post
try a combination formula like =Search("mango")AND("orange") - using whatever syntax excel uses. I use Lotus 123, where I would use a complex "IF" statement in a new column. That might work in excel.
That's what I was thinking - and IF statement.
__________________
Tude is offline   Reply With Quote
Old 06-29-10, 06:59 AM   #18
jccaclimber
Senior Member
 
Join Date: May 2005
Location: Terre Haute, Lafayette, or Indianapolis, IN, depending on the day
Bikes: n, I would like n+1
Posts: 1,917
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by ModoVincere View Post
Toilet, apple.....same thing.
You know, one make much better pies than the other.
jccaclimber is offline   Reply With Quote
Old 06-29-10, 07:06 AM   #19
ModoVincere
Riding Heaven's Highways on the grand tour
 
ModoVincere's Avatar
 
Join Date: Aug 2006
Bikes:
Posts: 1,675
Mentioned: 1 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by jccaclimber View Post
You know, one make much better pies than the other.
mud pies.....apple pies......what's your point?
__________________
1 bronze, 0 silver, 1 gold
ModoVincere is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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 Off



All times are GMT -6. The time now is 04:27 PM.