+ Reply to Thread
Results 1 to 5 of 5

Trying to match two strings of data

  1. #1
    Registered User
    Join Date
    06-19-2018
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Trying to match two strings of data

    Hi,

    thanks in advance...

    I have one piece of data, trying to match to a larger selection, to see what matches.

    E.g.
    Test: 00110001

    Comparing to:
    10110001 - TRUE - as the 3rd, 4th, 8th are "1" matches
    01010100 - TRUE - as the 4th is a "1" matches
    11111111 - TRUE - as the 3rd, 4th, 8th "1" matches
    00000010 - FALSE - as there are no "1" matches
    00000001 - TRUE - as the 8th is a "1" matches
    00000000 - FALSE - as there are no "1" matches
    11001110 - FALSE - as there are no "1" matches
    00100000 - TRUE - as the 3rd is a "1" match

    I'm trying to get a false/positive where the comparing data matches any 1 to a corresponding 1 in the test case. Just one match is enough for a TRUE result.

    Thanks so much, Sarah

  2. #2
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: Trying to match two strings of data

    Assuming your "test" is in cell A1, try this:

    =AND(--SUBSTITUTE(SUBSTITUTE($A$1+A3,1,0),2,1))

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Trying to match two strings of data

    Hi =00110001
    Try:

    =SUMPRODUCT((MID($H$1,{1,2,3,4,5,6,7,8},1)="1")*(MID(A2,{1,2,3,4,5,6,7,8},1)="1"))>0

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Trying to match two strings of data

    Or try:

    =ISNUMBER(SEARCH(2,$H$1+A2))

  5. #5
    Registered User
    Join Date
    06-19-2018
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Trying to match two strings of data

    Thanks to all the replies - all three solutions worked - really appreciate your help!

    Cheers, Sarah
    Last edited by ozharas72; 06-19-2018 at 02:19 AM. Reason: response while replying

+ 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. Compare to strings for a match (string data may not be in same order)
    By Kramxel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2016, 02:47 PM
  2. Match different long strings with an array of short, summarizing strings
    By FKemps in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2015, 12:55 AM
  3. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. [SOLVED] loop to match if shorter strings appears in longer strings
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2012, 08:07 PM
  5. Extracting strings from a column of data that match a predefined list
    By jmurray in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-16-2009, 05:10 AM
  6. Count if match any of 3 strings
    By GGG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-08-2005, 07:20 PM
  7. [SOLVED] Match data of two identical strings in two separate spreadhseets
    By Mahendra in forum Excel General
    Replies: 0
    Last Post: 09-14-2005, 07:05 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