replace characters and compare results in a formula

[inolab]

I know this is going to be an easy answer and I am almost there (I hope), but I need help because I can't get the correct result.

I have 2 columns of names and one column has 7 characters that need to be replaced and then the result of the first 3 characters compared to the other column to see if they are the same and if not - they need to be researched. Here is what I have so far and individually the formulas work - together not so much. Thanks in advance for your help.

[[TABLE="width: 1066"]
<tbody>[TR]
[TD]FINANCIAL NAME
[/TD]
[TD]LOG NAME
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]osu-aj HER NAME IS
[/TD]
[TD]YOUR NAME IS
[/TD]
[TD]=IF(A2="","","BLAH")
[/TD]
[TD]THIS LEAVES C BLANK IF CELL IN COLUMN A IS BLANK OTHERWISE "BLAH"
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ALL NAMES ARE
[/TD]
[TD]=REPLACE(A2,1,7,"")
[/TD]
[TD]THIS REPLACED 7 CHARACTERS IN FRONT OF THE NAME IN COLUMN A
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(A3="","",REPLACE(A3,1,7,""))
[/TD]
[TD]THIS LEAVES C BLANK IF A IS BLANK AND REPLACES FIRST 7 CHARACTERS OF NAME IF NOT BLANK
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(ISTEXT(SEARCH(LEFT(A2,3),(LEFT(A2,3)),1)),"","RESEARCH")
[/TD]
[TD]IF FIRST 3 CHARACTERS DON’T MATCH IN COLUMN A AND B, RESULT IS TO RESEARCH ]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

=IF(ISTEXT(SEARCH(LEFT(A2,3),(LEFT(A2,3)),1)),"","RESEARCH")

this is not comparing column A to column B.

 

[inolab]

Sorry - I manually changed the columns in my formula and missed changed that. Here is the corrected view.

[TABLE="width: 1066"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][B][U]FINANCIAL NAME[/U][/B][/TD]
[TD][B][U]LOG NAME[/U][/B][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]osu-aj HER NAME IS[/TD]
[TD]YOUR NAME IS[/TD]
[TD]=IF(A2="","","BLAH")[/TD]
[TD]THIS LEAVES C BLANK IF CELL IN COLUMN A IS BLANK OTHERWISE "BLAH"[/TD]
[/TR]
[TR]
[TD]OSU-AJ ALL NAMES ARE[/TD]
[TD]ALL NAMES ARE[/TD]
[TD]=REPLACE(A2,1,7,"")[/TD]
[TD]THIS REPLACED 7 CHARACTERS IN FRONT OF THE NAME IN COLUMN C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(A3="","",REPLACE(A3,1,7,""))[/TD]
[TD]THIS LEAVES C BLANK IF A IS BLANK AND REPLACES FIRST 7 CHARACTERS OF NAME IF NOT BLANK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=IF(ISTEXT(SEARCH(LEFT(A2,3),(LEFT(B2,3)),1)),"","RESEARCH")[/TD]
[TD]IF FIRST 3 CHARACTERS DON’T MATCH IN COLUMN A AND B, RESULT IS TO RESEARCH[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[JLGWhiz]

Try this for your comparison.

=IF(LEFT(A2,3)=LEFT(B2,3),"","RESEARCH")

 

[JLGWhiz]

In case you need to verify the first three characters are text, use this one.

=IF(AND(AND(ISTEXT(LEFT(A2,3)),ISTEXT(LEFT(B2, 3))),LEFT(A2,3)=LEFT(B2,3)),"","RESEARCH")

 

[inolab]

Try this for your comparison.

=IF(LEFT(A2,3)=LEFT(B2,3),"","RESEARCH")

This works for proving there is a difference in the text within those 2 cells because one column has a prefix and the other doesn't - but the names are actually the same (see A3 and B3) and you still get "research" with this formula. I have to incorporate removing the first 7 characters ("osu-aj " is the 7 characters) in column A before doing the comparison. The "osu-aj " will be listed in every cell in column A along with names but column B will not have that prefix in it and only the names and that difference is what I am trying to find. I believe this is where I get stuck - trying to combine the formulas into one cell that will start at characters 8-10 in column A and verify it against column B characters 1-3 and if they are different to say "research".

 

[dreid1011]

You could try using MID(A2,8,3)=LEFT(B2,3) ... This would eliminate the need to replace the first 7 characters.

And maybe combine it with what JLG suggested:

=IF(AND(AND(ISTEXT(MID(A2,8,3)),ISTEXT(LEFT(B2, 3))),MID(A2,8,3)=LEFT(B2,3)),"","RESEARCH")

And combine it with leaving it blank if col A is blank:

=IF(A2="","",IF(AND(AND(ISTEXT(MID(A2,8,3)),ISTEXT(LEFT(B2, 3))),MID(A2,8,3)=LEFT(B2,3)),"","RESEARCH"))

 

[inolab]

This works for proving there is a difference in the text within those 2 cells because one column has a prefix and the other doesn't - but the names are actually the same (see A3 and B3) and you still get "research" with this formula. I have to incorporate removing the first 7 characters ("osu-aj " is the 7 characters) in column A before doing the comparison. The "osu-aj " will be listed in every cell in column A along with names but column B will not have that prefix in it and only the names and that difference is what I am trying to find. I believe this is where I get stuck - trying to combine the formulas into one cell that will start at characters 8-10 in column A and verify it against column B characters 1-3 and if they are different to say "research".

I think I got it now. Thank you so much for your help with the formula you listed. It got me thinking in a different direction. Here is my resulting formula:
[(if((mid(A2,8,3))=left(B2,3),"","RESEARCH")]

 

[inolab]

I am eventually going to be an Excel guru like all of you

 

[inolab]

You could try using MID(A2,8,3)=LEFT(B2,3) ... This would eliminate the need to replace the first 7 characters.

And maybe combine it with what JLG suggested:

=IF(AND(AND(ISTEXT(MID(A2,8,3)),ISTEXT(LEFT(B2, 3))),MID(A2,8,3)=LEFT(B2,3)),"","RESEARCH")

And combine it with leaving it blank if col A is blank:

=IF(A2="","",IF(AND(AND(ISTEXT(MID(A2,8,3)),ISTEXT(LEFT(B2, 3))),MID(A2,8,3)=LEFT(B2,3)),"","RESEARCH"))

This is awesome. I think I just found a similar formula, but this is great help too