Advertise on Bikeforums.net



User Tag List

Results 1 to 19 of 19
  1. #1
    la vache fantôme phantomcow2's Avatar
    Join Date
    Aug 2004
    Location
    NH
    Posts
    6,266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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

  2. #2
    Senior Member
    Join Date
    May 2005
    Location
    Terre Haute, Lafayette, or Indianapolis, IN, depending on the day
    My Bikes
    n, I would like n+1
    Posts
    1,917
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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?

  3. #3
    Banned. ModoVincere's Avatar
    Join Date
    Aug 2006
    Posts
    1,628
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Try the = Find(find_text,within_text,start_num) function.

  4. #4
    That darn Yankee TexasGuy's Avatar
    Join Date
    Jun 2005
    Location
    West West Fort Worth
    My Bikes
    Mongoose XR-100, Eros Bianchi
    Posts
    4,286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.

  5. #5
    You Know!? For Kids! jsharr's Avatar
    Join Date
    Apr 2005
    Location
    Just NW of Richardson Bike Mart
    My Bikes
    '05 Trek 1200 / '90 Trek 8000 / '? Falcon Europa
    Posts
    6,083
    Mentioned
    12 Post(s)
    Tagged
    3 Thread(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.

  6. #6
    That darn Yankee TexasGuy's Avatar
    Join Date
    Jun 2005
    Location
    West West Fort Worth
    My Bikes
    Mongoose XR-100, Eros Bianchi
    Posts
    4,286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.

  7. #7
    la vache fantôme phantomcow2's Avatar
    Join Date
    Aug 2004
    Location
    NH
    Posts
    6,266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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

  8. #8
    That darn Yankee TexasGuy's Avatar
    Join Date
    Jun 2005
    Location
    West West Fort Worth
    My Bikes
    Mongoose XR-100, Eros Bianchi
    Posts
    4,286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.

  9. #9
    Banned. ModoVincere's Avatar
    Join Date
    Aug 2006
    Posts
    1,628
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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.

  10. #10
    Caustic Soccer Mom apclassic9's Avatar
    Join Date
    Jun 2005
    Location
    Millstone WV
    Posts
    1,759
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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.

  11. #11
    derailleurs are overrated bigbenaugust's Avatar
    Join Date
    Feb 2005
    Location
    KIGX
    My Bikes
    2009 Motobecane Fantom CX, 2012 Motobecane Fantom Cross Uno SSCX
    Posts
    1,727
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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
    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)
    Current Linux Usage (by machine): Arch: I openSUSE: III

  12. #12
    You Know!? For Kids! jsharr's Avatar
    Join Date
    Apr 2005
    Location
    Just NW of Richardson Bike Mart
    My Bikes
    '05 Trek 1200 / '90 Trek 8000 / '? Falcon Europa
    Posts
    6,083
    Mentioned
    12 Post(s)
    Tagged
    3 Thread(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.

  13. #13
    Banned. ModoVincere's Avatar
    Join Date
    Aug 2006
    Posts
    1,628
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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?

  14. #14
    You Know!? For Kids! jsharr's Avatar
    Join Date
    Apr 2005
    Location
    Just NW of Richardson Bike Mart
    My Bikes
    '05 Trek 1200 / '90 Trek 8000 / '? Falcon Europa
    Posts
    6,083
    Mentioned
    12 Post(s)
    Tagged
    3 Thread(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.

  15. #15
    Banned. ModoVincere's Avatar
    Join Date
    Aug 2006
    Posts
    1,628
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Toilet, apple.....same thing.

  16. #16
    derailleurs are overrated bigbenaugust's Avatar
    Join Date
    Feb 2005
    Location
    KIGX
    My Bikes
    2009 Motobecane Fantom CX, 2012 Motobecane Fantom Cross Uno SSCX
    Posts
    1,727
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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
    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)
    Current Linux Usage (by machine): Arch: I openSUSE: III

  17. #17
    Blasted Weeds Tude's Avatar
    Join Date
    Aug 2006
    Location
    Rochester, NY
    My Bikes
    Trek 1200C, Specialized Rockhopper, Giant Yukon FX, Giant Acapulco
    Posts
    1,183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.

  18. #18
    Senior Member
    Join Date
    May 2005
    Location
    Terre Haute, Lafayette, or Indianapolis, IN, depending on the day
    My Bikes
    n, I would like n+1
    Posts
    1,917
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ModoVincere View Post
    Toilet, apple.....same thing.
    You know, one make much better pies than the other.

  19. #19
    Banned. ModoVincere's Avatar
    Join Date
    Aug 2006
    Posts
    1,628
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •