+ Reply to Thread
Results 1 to 16 of 16

How to Search for Text within Text with Case Insensitive AND No Partial Searches?

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    I looked online but couldn't find a solution. I am looking for the best of both worlds from the Find and Search function.

    Find: It gives me exact match and is case sensitive
    Search: It gives me partial searches and is case insensitive

    I want to search for results that are exact matches and case insensitive.

    Here is the search formula I am currently using:

    =IF(ISNUMBER(SEARCH("excel",$A2485,1)),1,0)

    UPDATE:

    I am a bit wrong in my conclusions. I see that for the Find function, if I search for the word "Excel" with the E capitalized, the word is "found" in the word Excellent, which I don't want.

    Sample Title: 45 Ways to Excellent Life

    I want the word "Excel" to be found only if the entire / whole word Excel is in that string / line / cell, regardless if I am searching for (if the formula has this) Excel, excel, EXCel, and regardless if the text being searched has Excel, excel, EXCell, EXCellent, etc.

    Sample Title: 45 Ways to Excellent Life : Result should be not found
    Sample Title: 45 Ways to excellent Life : Result should be not found
    Sample Title: I love Excel, math and science : Result should be found
    Sample Title: I love excel, math and science : Result should be found
    Last edited by roseuz; 07-27-2017 at 07:03 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Sorry if I am being a bit dim here, but haven't you answered your own question? Use SEARCH in one set of results and FIND in the other, surely? Or am I missing something?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,006

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    =IF(ISNUMBER(MATCH("excel",$A2485,0)),1,0)

    shoudl do that...
    Glenn



  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Are we searching for single word in a multi-word text string ?

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    I have updated my original post to clarify exactly what I am hoping to accomplish.
    Last edited by AliGW; 07-27-2017 at 07:18 AM. Reason: Unnecessary quotations removed for readability.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    See post #4.

  7. #7
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    I found what I was looking for.

    =ISNUMBER(SEARCH(" Word "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),";",""),"!","")&" "))

    View the Source link for more details:

    Source: https://superuser.com/a/479533

    To those, well, just one, that tried to offer an actual solution, Thank you.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,006

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Mmmm. Not convinced. this might be a morerobust alternative:

    Please Login or Register  to view this content.


    -FindEngWord(A1,"excel")
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-30-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Thanks. I will test this some other time. I figured it wasn't the best, as it would have to account for every single punctuation / symbol that exists. Right now it didn't pickup the word I was searching for because it had a colon after it, which the formula didn't have.
    Last edited by AliGW; 07-27-2017 at 07:44 AM. Reason: Unnecessary quotation removed.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,006

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Certainly you'll need one more SUBSTITUTE, to cope with:

    = yadda yadda yadda excel.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Rose - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Perhaps regular expressions would help
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    With the VA supplied ..

    Sample Title: 45 Ways to Excel.
    Sample Title: 45 Ways to excellent Life : Result should be not found
    Sample Title: I love Excel. math and science : Result should be found
    Sample Title: I love excel, math and science : Result should be found
    Sample Title: I love excel: math and science : Result should be found
    Sample Title: I love excel; math and science : Result should be found

    All results are TRUE (correct) excepted highllighted

  14. #14
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    Hi all- How about this?:

    SEARCH allows wildcards (?, *). In normal sentence structure, a word terminates with a space, or with a single punctuation mark followed by a space. Check for both. Add a space to the end of the search_in text to catch words at the end of a string.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    PS NOTE- The above verifies the END of the word. To verify the BEGINNING too, use this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 07-27-2017 at 09:24 AM.

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,550

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    OR TRY
    =IFERROR(IF(AND(FIND("Excel",A1),OR(CODE(MID(A1,SEARCH("Excel",A1)+5,1))<65,CODE(MID(A1,SEARCH("Excel",A1)+5,1))>122),OR(CODE(MID(" "&A1,SEARCH("Excel"," "&A1)-1,1))<65,CODE(MID(" "&A1,SEARCH("Excel"," "&A1)-1,1))>122)),"Found","Not Found"),"Not Found")
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  16. #16
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,550

    Re: How to Search for Text within Text with Case Insensitive AND No Partial Searches?

    sorry ignore post no 15
    try
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. search partial text
    By shaifshahin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2016, 12:17 PM
  2. search partial text
    By shaifshahin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2016, 06:41 AM
  3. Partial text search
    By dawsexcel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-23-2014, 12:50 PM
  4. [SOLVED] finding partial text ,In the case of more than one match
    By fartdog01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2014, 09:20 AM
  5. Search for Partial Text
    By grkchakri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2012, 06:47 AM
  6. Case insensitive search for string within folder of excel files
    By vba_novice in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-22-2011, 11:21 PM
  7. [SOLVED] please help Need VBA code that searches for partial text in a cell
    By Shani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-13-2006, 11:25 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1