Excel - IF Formula with a FIND

Question

While I wait for my firm to develop an automated process for this, I have an excel document that I must process on a regular basis. We recently discovered a problem where the formula I'm using strips can only return #VALUE as a result. when the FIND formula cannot locate the text I am looking for.

the formula we currently have is:

=IF(FIND("-",M2,3),RIGHT(M2,2))

The states and provinces that are present in the cells that this formula examines look like "CA-ON" or "US-NV." The UK's regions are filled up as "UK-XX," rather than actual counties like "Essex" or "Merryside," which is a concern. What I need the formula to do is take whatever value is present and write it in the cell the formula is in if it can't find the hyphen(-) in the cell.

As this is an optional field, I should also point out that part of the cells is blank. Is it possible to run this formula such that, in the absence of the "-," it simply outputs the value that is present?

Answer 1

What about using mid() to see if the third character is "-"

=IF(MID(A1,3,1)="-",RIGHT(A1,2),A1)

If you really want to use the find() function then:

=IF(ISNUMBER(FIND("-",A1)),RIGHT(A1,2),A1)