+ Reply to Thread
Results 1 to 11 of 11

Identify cells that contain partially matching text?

  1. #1
    Forum Contributor
    Join Date
    05-21-2014
    Location
    United States
    MS-Off Ver
    Office 365 on Windows 10
    Posts
    122

    Question Identify cells that contain partially matching text?

    For example:

    Identify these as a match:

    Cell A1 = Google Inc.
    Cell B1 = Google.com


    Identify these as a non-match:

    Cell A1 = Yahoo Inc.
    Cell B1 = Google.com


    Basically, a match would be where a cell has at least a certain amount of characters in order that match.

    Is this possible?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Identify cells that contain partially matching text?

    It is possible but may require VBA. To provide a solution, you need a much more comprehensive description. What is the "certain amount"? Does the match have to be at the beginning of the string, or could it occur anywhere within the strings?
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    05-21-2014
    Location
    United States
    MS-Off Ver
    Office 365 on Windows 10
    Posts
    122

    Re: Identify cells that contain partially matching text?

    It can be anywhere in the string. The amount would probably be 4 or more, but hopefully the script would be easy enough to change this if necessary.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Identify cells that contain partially matching text?

    Do a Google search on "Excel fuzzy match" or "Excel fuzzy lookup".
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Contributor
    Join Date
    05-21-2014
    Location
    United States
    MS-Off Ver
    Office 365 on Windows 10
    Posts
    122

    Re: Identify cells that contain partially matching text?

    Thanks. I downloaded the Fuzzy add in, but I can't seem to enable it. It tells me a runtime error occurred during the loading of the COM Add In.

    Anyone know how to fix this?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Identify cells that contain partially matching text?

    Sorry, no suggestions.

  7. #7
    Registered User
    Join Date
    01-28-2015
    Location
    CA
    MS-Off Ver
    2007
    Posts
    67

    Re: Identify cells that contain partially matching text?

    Can you upload your workbook so i can see the actual data?
    the only way i'll probably be able to make this plausible is creating a VBA code or lookup table depending on how complex it is.

    otherwise use this:

    =IF(AND(ISNUMBER(SEARCH("google",A1)),ISNUMBER(SEARCH("google",A2))), "Yes", "No")
    but change "google" to whatever words you would believe match
    Last edited by BlakeSkate; 04-27-2015 at 07:50 PM.

  8. #8
    Forum Contributor
    Join Date
    05-21-2014
    Location
    United States
    MS-Off Ver
    Office 365 on Windows 10
    Posts
    122

    Re: Identify cells that contain partially matching text?

    Unfortunately I cannot upload the workbook because it contains sensitive information.

    I've managed to successfully install Fuzzy Lookup add in, but I can't figure out how to get it to do what I need it to do.

    Column A contains company names and Column B contains email addresses. When I run Fuzzy Lookup, it looks at email address and finds one similar match in the company name column.

    What I need it to do is look at the email address and company name in the same row and verify whether or not there is a fuzzy match between only those 2 values.

    So for example, it would look at cell A2 and B2 and see if there is a match, look at cell A3 and B3 and see if they match, etc.

    Anyone know if this is possible using this add in?

    Or if this is not possible, how about a formula that will look at the first few characters in cell B2 and see if they exist anywhere in cell B1 in the same order?
    Last edited by MetroBOS; 04-28-2015 at 03:46 PM.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Identify cells that contain partially matching text?

    Quote Originally Posted by MetroBOS View Post
    Or if this is not possible, how about a formula that will look at the first few characters in cell B2 and see if they exist anywhere in cell B1 in the same order?
    Here is a very simple approach that does that. Change the 4 to however many characters you want to check.

    =IF(NOT(ISERROR(FIND(LEFT(B2,4),B1))),"MATCH","")

    Edit: I went by your description literally, though earlier you said the two values were in the same row.

    Edit again: Simplified logic, and made case-insensitive

    =IF(ISERROR(FIND(LEFT(UPPER(A1),4),UPPER(B1))),"","MATCH")
    Last edited by 6StringJazzer; 04-28-2015 at 04:31 PM. Reason: as shown

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Identify cells that contain partially matching text?

    Here is code for a function that will compare two strings and return TRUE if any consecutive x characters in one string matches x consecutive characters in another string. Case insensitive.

    Use:

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


    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    05-21-2014
    Location
    United States
    MS-Off Ver
    Office 365 on Windows 10
    Posts
    122

    Re: Identify cells that contain partially matching text?

    This is brilliant! Thank you so much! I owe you one.

+ 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. Replies: 3
    Last Post: 07-10-2014, 08:11 PM
  2. [SOLVED] TEXT Formula to identify rows of cells with Matching TEXT *** Actual Worksheet attached.
    By steve08087 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2013, 12:42 PM
  3. Replies: 3
    Last Post: 10-02-2011, 05:06 PM
  4. [SOLVED] How do I identify cells with matching numbers within a column.
    By dbmeyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-26-2006, 06:40 PM

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