Matching a partial string

S

steedy

New Member
Joined
Jun 19, 2015
Messages
8
Hi,

First time here, I hope somebody can help me. I have 2 Columns, in Column A I have a string that is 12 characters in length (fixed), in column B I could have exactly the same string, I need to make sure that A2 string = B2, A3 =B3 etc..... if I use the exact function this replies with a true or false answer which is great. However what i need is a true or false answer if 9 or more of the characters match, in the example below the 1st and last strings obviousley fail with the Exact function however I need them to pass as >9 match.

Any examples are greatfully received.

[TABLE="width: 322"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]F0FXE98MUBQF
[/TD]
[TD]J0FXE98MJBQF[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]1ZP0XDJYKG5K[/TD]
[TD]1ZP0XDJYKG5K[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]GV7LRZKJGCY3[/TD]
[TD]GV7LRZKJGCY3[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]XBLD06NNNTDS[/TD]
[TD]XBLD06NNNTDS[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]G9LF2Y4KP49B[/TD]
[TD]G9LF2Y4KP49B[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]VGLE5JYKXFVM[/TD]
[TD]VGLE5JYKXFVM[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]291SN1Y8V5AN[/TD]
[TD]291SN1Y8V5AN[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]CY97EBEE46TZ[/TD]
[TD]CY97EBEE46TZ[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]NFXH3KSQC4UE[/TD]
[TD]NFXH3KSQC4UE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]S0VM75RDELAR[/TD]
[TD]S0VM75RDELAR[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]QZL4H7GNH94A[/TD]
[TD]QZL4H7GNH94A[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]CMRN1YSJQT2Z[/TD]
[TD]CMRN1YSJQT2Z[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]FTHAAPMLN8JF
[/TD]
[TD]THAAPMLN8JF[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 258"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hi.

Just to clarify: you don't mean that 9 or more characters have to match and also occupy the same position within the string, or even the same relative position to each other? If this was the case, your last example would not pass the test.

It would also mean that:

ABCDEFGHIJ

and

IEADFBGCJX

would be considered a match.

Can you just confirm this?

Regards
 
Upvote 0
V_Malkoti said:
Looking at examples he gave, I think that's exactly what he means.
Click to expand...

Perhaps, but then they wouldn't be much of a match, would they?

And if my assumption was false, then we might be getting into the murky area of "fuzzy matching", unless of course the OP can come up with a more rigorous definition of what constitutes 9 or more characters "matching" for a given pair of strings.

Regards
 
Upvote 0
Thanks for the replies so far, the characters would not be mixed up as per the
ABCDEFGHIJ

and

IEADFBGCJX


so relative position would be the same unless the 1st character was missed and then the whole string would shift by 1 place, in this instance if it is to complicated it doesnt matter, so lets say the string is always 12 characters long and 9 out of the 12 must match and have the same relative position. Is there a way to do this ??
 
Upvote 0
So can you give some more varied examples together with your expected results, just so it's clear?

Regards
 
Upvote 0
Hi, so I've looked at this a bit more and think for ease we should say both strings should contain 12 characters, if 9 out of the 12 have the same relative position then it gives a true response. Here are some example codes.

Using the Exact function this has highlighted 3 strings that give a False but actually should return a True

[TABLE="width: 381"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]F0FXE98MUBQF
[/TD]
[TD]J0FXE98MJBQF
[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]1ZP0XDJYKG5K[/TD]
[TD]1ZP0XDJYKG5K
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]GV7LRZKJGCY3[/TD]
[TD]GV7LRZKJGCY3[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]VGLE5JYKXFVM[/TD]
[TD]VGLE5JYKXFVM[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]291SN1Y8V5AN[/TD]
[TD]291SN1Y8V5AN[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]CY97EBEE46TZ[/TD]
[TD]CY97EBEE46TZ[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]NFXH3KSQC4UE[/TD]
[TD]NFXH3KSQC4UE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]S0VM75RDELAR[/TD]
[TD]S0VM75RDELAR[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]QZL4H7GNH94A[/TD]
[TD]QZL4H7GNH94A[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]CMRN1YSJQT2Z[/TD]
[TD]CMRN1YSJQT2Z[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]V9S9MFKAW02D[/TD]
[TD]V9S9MFKAW02D[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]KCEARLJJ6J21[/TD]
[TD]KCEARLJJ6J21[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]L48ZT6B5DB79[/TD]
[TD]L48ZT6B5DB79[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]KYBERTFDGD4X[/TD]
[TD]KYBERTFDGD4X[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]B1C6LXY2Z6S8[/TD]
[TD]B1C6LXY2Z6S8[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]0VNN86R29C8J[/TD]
[TD]0VNN86R29C8J[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]A7BMKT8YPN0Z[/TD]
[TD]A7BMKT8YPN0Z[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1NW3FWG9CWKE[/TD]
[TD]1JW3FWG9CWKE
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]5C5DKA1CJP5E[/TD]
[TD]5C5DKA1CJP5E[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]A9CDNG73TPWC[/TD]
[TD]A9CDNG73TPWC[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]39X3R6KB82AT[/TD]
[TD]39X3R6KB82AT[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]35L9H4SLZYSD[/TD]
[TD]35L9H4SLZYSD[/TD]
[TD="align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD]N5W4D9WXJKPB[/TD]
[TD]J5W4D9WXJKPB
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]



More FALSE examples, the one highlighted in RED really is false

[TABLE="width: 381"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]F7M61Q0J5GEV
[/TD]
[TD]J7M61Q0J5GEV
[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]F4Q1UX21YFG9[/TD]
[TD]J4Q1UX21YFG9
[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]R4XPNT40TDAS
[/TD]
[TD]R4XPJJ14QTDA[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]MEEM0MMEXYX9[/TD]
[TD]JEEM0MMEXYX9
[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]HC6KPTZ27K8V[/TD]
[TD]JC6KPTZ27K8V
[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]E8ZLG3GRRHWC[/TD]
[TD]J8ZLG3GRRHWC[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]Z8TEBCKMMU44[/TD]
[TD]J8TEBCKMMU44[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]R5YFTW2RWZEC[/TD]
[TD]P5YFTW2RWZEC[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]E0UY5TC4HDFL[/TD]
[TD]J0UY5TC4HDFL[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]GZNB6YYK6N0R[/TD]
[TD]ZJJB6YYK6N0R[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]H8FS8PJJUKVH[/TD]
[TD]J8FS8PJJUKVH[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]45ESU9SKTZ3F[/TD]
[TD]45ESJ9SKTZ3F[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]HL96YNXFG8L2[/TD]
[TD]JL96YNXFG8L2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]7U28Y2KYCPUY[/TD]
[TD]4U28Y2KYCPUY[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]DULDANPCW4B6[/TD]
[TD]DJLDANPCW4B6[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]U6DH0ADP2622[/TD]
[TD]J6DH0ADP2622[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]R83PNV3D9EQ5[/TD]
[TD]P83PNV3D9EQ5[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]H6MXVN638PWR[/TD]
[TD]J6MXVN638PWR[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]XBWKJY9XRE7M[/TD]
[TD]KBWKJY9XRE7M[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]T5J4XX317F4D[/TD]
[TD]J5J4XX317F4D[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]KU4EFDAM0HF3[/TD]
[TD]KD4EFDAM0HF3[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]UXENVWQ0X0Z3[/TD]
[TD]JXENVWQ0X0Z3[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]VCZJNXNTTXN5[/TD]
[TD]WCZJNXNTTXN5[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]H45EE87DNBXD[/TD]
[TD]J45EE87DNBXD
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD]N2GVRMDSSAZ2
[/TD]
[TD]J2GVRMDSSAZ2
[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 381"]
<tbody>[TR]
[TD="width: 127"]A8KPX8Y1NMVV
[/TD]
[TD="width: 127"]A8KPX8K1TNMV
[/TD]
[TD="width: 127, align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
steedy said:
..if 9 out of the 12 have the same relative position then it gives a true response.
Click to expand...
I must be reading something wrong here, but based on what you say above, I think every one of your examples are TRUE. Here is the array-entered** formula I used to get those results...

=SUM(0+(MID(A1,ROW(1:12),1)=MID(B1,ROW(1:12),1)))>=9

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Searching For File Strings In Folders Is Too Broad
Replies
5
Views
159
Ark68
A
R
  • Solved
Compare two ranges in Excel to see if they match exactly
2 3
Replies
25
Views
579
HighAndWilder
H
I
  • Question Question
Unable to run VBA code
Replies
6
Views
319
RoryA
RoryA
P
  • Solved
Lambda and custom sequence with interference of string
Replies
3
Views
345
StephenCrump
S
D
  • Question Question
Locking of cells after not working
2
Replies
11
Views
294
Dazzatron
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top