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 in Others by Kithuzzz
• 20,660 points
53 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 by narikkadan
• 37,660 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 6 days ago in Others by narikkadan
• 37,660 points
27 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 6 days ago in Others by narikkadan
• 37,660 points
34 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
201 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
76 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 in Others by Edureka
• 13,640 points
139 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 in Others by Edureka
• 13,640 points
234 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 in Others by Edureka
• 13,640 points
102 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18 in Others by gaurav
• 22,040 points
69 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 in Others by narikkadan
• 37,660 points
54 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 in Others by narikkadan
• 37,660 points
24 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