+ Reply to Thread
Results 1 to 13 of 13

Compare all characters from two strings and get position of mismatchs

  1. #1
    Registered User
    Join Date
    01-19-2021
    Location
    Quebec
    MS-Off Ver
    O365
    Posts
    5

    Smile Compare all characters from two strings and get position of mismatchs

    Hello,

    I am looking for a way to find the position of mismatchs in two strings of characters.

    ABCDEFG : Reference string
    ABBDEFH : Should return "3, 6"
    ACDEFH : Should return "2, 6"

    Would that be possible to do?
    Thank you !

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Compare all characters from two strings and get position of mismatchs

    If A1 IS ABBDEFH

    =TEXTJOIN(", ",TRUE,IF(MID(A1,ROW($F$1:$F$7),1)=MID("ABCDEFG",ROW($F$1:$F$7),1),"",ROW($F$1:$F$7)))
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Compare all characters from two strings and get position of mismatchs

    This comparison would be easy because they are the same length and it would be a one-to-one comparison based on position.
    ABCDEFG : Reference string
    ABBDEFH : Should return "3, 6"

    This comparison would be hard to identify the missing letter B and recognize CDEF as matches because they are out of position.
    ABCDEFG : Reference string
    ACDEFH : Should return "2, 6"
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Contributor
    Join Date
    07-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    256

    Re: Compare all characters from two strings and get position of mismatchs

    Can you try this macro sir

    Paste these words in A1,A2,A3 etc
    Please Login or Register  to view this content.
    Last edited by catchnanan; 01-20-2021 at 01:48 PM.

  5. #5
    Forum Contributor
    Join Date
    07-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    256

    Re: Compare all characters from two strings and get position of mismatchs

    Dear experts if there are any changes in my code please suggest
    or modify the code to give output
    This is my attempt to help with a macro...

  6. #6
    Registered User
    Join Date
    01-19-2021
    Location
    Quebec
    MS-Off Ver
    O365
    Posts
    5

    Re: Compare all characters from two strings and get position of mismatchs

    Thank you mehmetcik! Your formula does work very well when there are no characters missing. I will definitely use that until I can find a solution that will also consider missing characters.
    I am having trouble figuring out the purpose of the F1:F7 range? My data can have up to a few hundred characters to compare, do I need to set that range to as much empty cells as there are characters to compare ?
    Thank you !

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Compare all characters from two strings and get position of mismatchs

    Hi.

    I used that to get 7 characters out of your 7 character sample.

    So if your reference string can be any length then we need a small change.

    I will modify the formula.

    =TEXTJOIN(", ",TRUE,IF(MID(A1,ROW(INDIRECT("A1:A" & LEN(A1))),1)=MID("ABCDEFGbv",ROW(INDIRECT("A1:A" & LEN(A1))),1),"",ROW(INDIRECT("A1:A" & LEN(A1)))))
    Last edited by mehmetcik; 01-20-2021 at 02:07 PM.

  8. #8
    Registered User
    Join Date
    01-19-2021
    Location
    Quebec
    MS-Off Ver
    O365
    Posts
    5

    Re: Compare all characters from two strings and get position of mismatchs

    Thank you, I really appreciate your help. I will leave the thread open for a little while in case someone has an idea about how to consider missing characters, but I definitely have a really good starting point with your solution.
    I will try to do a workaround using your formula to find the missing characters' position and replace them with an asterix or something so that the rest of the characters shift to the right position.

    Cheers !

  9. #9
    Registered User
    Join Date
    01-19-2021
    Location
    Quebec
    MS-Off Ver
    O365
    Posts
    5

    Re: Compare all characters from two strings and get position of mismatchs

    Quote Originally Posted by catchnanan View Post
    Dear experts if there are any changes in my code please suggest
    or modify the code to give output
    This is my attempt to help with a macro...
    Thank you for your suggestion, the macro did not work for me but I will look further into it to find out why. Thank you !

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Compare all characters from two strings and get position of mismatchs

    ABCDEFG : Reference string
    ABBDEFH : Should return "3, 6"
    The 3rd letter in 1st string is missing but not the 6th, the 7th is. So should that be 3 and 7
    or
    The 3rd letter in the 2nd string is different and the 7th letter is different. So again that also makes it 3 and 7

    ABCDEFG : Reference string
    ACDEFH : Should return "2, 6"
    If you mean missing letters in the 2nd string compared to the 1st string, again it should be 2 and 7 again
    Your title says "mismatch" which means 2, 3, 4, 5 and 6 in the 3rd string (compared to the 1st string)

    Or do I see things wrong here?

  11. #11
    Registered User
    Join Date
    01-19-2021
    Location
    Quebec
    MS-Off Ver
    O365
    Posts
    5

    Re: Compare all characters from two strings and get position of mismatchs

    Quote Originally Posted by jolivanes View Post
    ABCDEFG : Reference string
    ABBDEFH : Should return "3, 6"
    The 3rd letter in 1st string is missing but not the 6th, the 7th is. So should that be 3 and 7
    or
    The 3rd letter in the 2nd string is different and the 7th letter is different. So again that also makes it 3 and 7

    ABCDEFG : Reference string
    ACDEFH : Should return "2, 6"
    If you mean missing letters in the 2nd string compared to the 1st string, again it should be 2 and 7 again
    Your title says "mismatch" which means 2, 3, 4, 5 and 6 in the 3rd string (compared to the 1st string)

    Or do I see things wrong here?
    Sorry, you are right it should be "3, 7" for the first example.
    ACDEFH should return 2 and 7 because there is a missing character and a switched character.

    I am actually trying to compare DNA sequences, and we want to be able to know at which position there was a switch, insertion or deletion of a DNA "character". I found a similarity score UDF that calculates the number of mismatchs that works really well but I am now trying to pinpoint their location relative to the reference sequence.

  12. #12
    Registered User
    Join Date
    12-06-2020
    Location
    Forl', Italy
    MS-Off Ver
    Office 2019
    Posts
    20

    Re: Compare all characters from two strings and get position of mismatchs

    It is ridiculous:

    If ABBDEFH --> 3, 6 means mismatch at positions 3 and 6 Then
    ACDEFH --> 2, 6 should indicate mismatch at positions 2 and 6, which is FALSE

    It's only a matter of logic and common sense.


    I think it should be better:

    ABBDEFH : 3, 6 = position mismatch
    ACDEFH : (2) = missing position 2 and no mismatch

    Bruno

  13. #13
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Compare all characters from two strings and get position of mismatchs

    Maybe this UDF could get you started:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-20-2021 at 05:51 PM.

+ 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. find letter and position in the matrix of the 10 strings of 6 digits.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-24-2018, 12:16 PM
  2. Replies: 4
    Last Post: 05-30-2015, 11:45 AM
  3. Find the position of various characters in string
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-11-2014, 12:12 PM
  4. Returning few characters from a certain position
    By Tabraiz in forum Excel General
    Replies: 2
    Last Post: 11-09-2011, 11:35 AM
  5. Strings - Find position of the nth occurrence
    By efernandes67 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2009, 05:46 PM
  6. Finding Start Position of string in list of strings
    By longfisher in forum Excel General
    Replies: 5
    Last Post: 05-25-2007, 10:34 PM
  7. [SOLVED] characters and strings
    By elicamacho in forum Excel General
    Replies: 4
    Last Post: 03-20-2006, 02:25 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