How to compare only a part of strings in different excel sheets(columns)

Question

How to compare two sheet columns specied below, however only part of string in location(column)in sheet1 will matches only part of string with the Location(column) in sheet2?
1.only 1st two characters of location(column) in sheet1 and 1st two characters of location(column) in sheet2 should match.
2.only any two characters of location(column) in sheet1 and any two characters of location(column) in sheet2 should match. Please help

Location(sheet1)    Location(sheet2)     
____________________________________________
india- north        USxcs  
India-west          Indiaasd  
India- east         Indiaavvds  
India- south        Africassdcasv  
US- north           Africavasvdsa  
us-west             UKsacvavsdv  
uk- east            Indiacascsa  
uk- south           UScssca  
Africa-middle       Indiacsasca  
Africa-south        Africaccc  
Africa-east         UKcac

Try something like this- =Vlookup(A, B, MatchingColumnName, True/False) here: A=desired substring from Sheet1 Location column obtained by relevant excel function(something like SubString() function) B=desired substring from Sheet2 Location column obtained by relevant excel function(something like SubString() function) (I am just giving an idea, please check for correct syntax) — vpv
Here the location of substring is unknown in Question 2. need to compare two tables if any part(4 characters) of string matches. — neobee
Hi, I think I've technically answered your question, even if you had problems expanding the answer. I'm happy to help you with that if you want to post the code, but I think you ought to accept my answer unless you disagree. — OlduwanSteve
Which version of Excel are you using, if your using 2003, you can only nest 7 IF functions so the solution by OlduwanSteve will not work for you, if that is the case you should update your question to indicate your version. — James Jenkins

OlduwanSteve OlduwanSteve · Accepted Answer · 2012-09-12T11:17:50

For question 1 you can use MID function to extract the first two characters from each cell value and compare them.

For question 2 there is a solution if you can accept a predetermined maximum length of string. It is not a very nice solution! You can use nested if statements, basically 'unrolling the loop'. This example compares cell A1 and B1 for lengths of A1 up to 12 characters:

=IF(IFERROR(FIND(MID(A1,1,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,2,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,3,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,4,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,5,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,6,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,7,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,8,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,9,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,10,2),B1,1),0)>0,TRUE,
IF(IFERROR(FIND(MID(A1,11,2),B1,1),0)>0,TRUE,
FALSE
)
)
)
)
)
)
)
)
)
)
)

Thanks to James Jenkins for this update

It seems older versions of Excel have a limit of 7 nested functions. You can work around this (if you don't mind making your spreadsheet even more ugly) by chaining together formulas in adjacent cells. In fact if you wanted to get really creative you could use the column index to calculate the offsets for the search, something like:

=IF(IFERROR(FIND(MID($A1,(COLUMN(C1) - 3) * 6 + 1, 2), $B1, 1),0)>0,TRUE,
...repeat with +2, +3, +4, +5
if(D2 = FALSE, FALSE, TRUE)
)))))))

Then the column can be copied right if you ever need more string length. Note the innermost 'if' should force a TRUE or FALSE value when the adjacent column is blank.

How to compare only a part of strings in different excel sheets(columns)

1 Answers