IF formula returning only false condition value

[Nohemi5280]

Hi,

I am trying to figure out why this formula is not working as intended.   

=IF(A2="Note",(RIGHT(A2,(LEN(A2)-42))),(RIGHT(A2,(LEN(A2)-49))))

If the cell contains the word "Note" then I want it to remove the first 42 characters and if the cell does not contain the word "Note" then I want it to remove the first 49. However the formula is removing the first 49 whether the word Note is there or not.

[#2 · Accepted solution]

A2="Note" checks if cell A2 is equal to "Note". If you want to check if "Note" is anywhere in cell A2 then use ISNUMBER(SEARCH("Note",A2)).

[#3]

@Detlef Lewin 

Thanks that did the trick in recognizing both conditions. My final formula is =IF(ISNUMBER(SEARCH("Note",A4)),(RIGHT(A4,(LEN(A4)-42))),IF(ISNUMBER(SEARCH("Deed",A4)), (RIGHT(A4,(LEN(A4)-47)))))

[#4]

@Nohemi5280 

As variant

=RIGHT( A4, LEN(A4) - 47 + 5*ISNUMBER( SEARCH( "Note", A4 ))  )