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?

Sep 26, 2022 in Others 598 views

## 1 answer to this question.

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)
```

• 63,700 points

## Excel formula to replace or stubstitute only text that starts with a certain letter

You're requesting a "formula." Normally, you would ...READ MORE

## Excel Conditional Formating to find numbers a cell with text

Try this: =OR(ISNUMBER(-MID(SUBSTITUTE(A1," ","~")&"~",seq,4))) where seq is a defined name that ...READ MORE

## Formula in Excel with a particular increment value

I need to use Excel formulas for ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE