Excel - IF Formula with a FIND

0 votes

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 by Kithuzzz
• 38,010 points
598 views

1 answer to this question.

0 votes

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

answered Sep 27, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,700 points
712 views
0 votes
1 answer

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

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

answered Nov 21, 2022 in Others by narikkadan
• 63,700 points
324 views
0 votes
1 answer

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

answered Jan 10, 2023 in Others by narikkadan
• 63,700 points
345 views
0 votes
0 answers

Formula in Excel with a particular increment value

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

Apr 10, 2023 in Others by Kithuzzz
• 38,010 points
250 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,135 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,636 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
797 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,831 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,700 points
681 views
0 votes
1 answer

Text with a dash in the cell but not in the formula bar (Excel)

What you have got here is called ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 63,700 points
1,913 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP