CODEWITHSUNDEEP
excelexcel-formula
Fate Averie
Fate Averie

Reputation: 359

Excel - IF Formula with a FIND

I have an excel document I have to process regularly, while awaiting my company to build an automated process for this, and the issue we recently found is that the formula I'm using strips can't return a result other than #VALUE! when the FIND formula fails to find the text I need it to.

the formula we currently have is:

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

The cells this formula checks have states, & provinces in them which look like so "CA-ON" or "US-NV". The problem is that regions for the UK don't fillout as "UK-XX" it inputs the actual county for example "Essex" or "Merryside"

What I need the formula to do is, if it can't find the hyphen(-) in the cell, then it should just take whatever value is there and write it in the cell the formula is in.

I should also mention that some of the cells are also blank, since this is an optional field. Is there anyway to run this formula where if it doesn't find the "-" it just writes whats there?

Upvotes: 0

Views: 19472

Answers (2)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

The IFERROR Function should help here. And there isn't a reason to use the if statement anymore. The formula below will find the hyphen if it is in the first 3 characters, and find the length of the string minus the location of the hyphen and return that string. The IFERROR will catch the instances where there is no hyphen and return your original cell.

=IFERROR(RIGHT(M2,LEN(M2)-FIND("-",LEFT(M2,3),1)),M2)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

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

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

enter image description here

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

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

enter image description here

Upvotes: 6

Related Questions