Microsoft Excel - Adding one more variable to an IF statement.

=IFERROR(IF(B6="",C5,MID(B6,(FIND("-",B6)+2),3))*1,IFERROR((MID(B6,(FIND("F",B6)-4),4))*1,(MID(B6,(FIND("A",B6)+1),3))*1))


I need to add another IF or IFERROR to the line to look for 20 O and 20 S so I can separate them accordingly.  or I just need a different way to strip out the data looking for Ideas.


DATA:                         Stripped out value:

6747 - 20 OSS	20
7199 - VMFA251	251
7134 - Seymour Johnson-336 FS	336
6861 - 157FS	157
4587 - 20 SFS	20

Hello Donald,
=IFERROR(IF(B6="",C5,IF(MID(B6,(FIND("-",B6)+2),3)*1=20,MID(B6,(FIND("-",B6)+2),4),IFERROR((MID(B6,(FIND("F",B6)-4),4))*1,(MID(B6,(FIND("A",B6)+1),3))*1))),IFERROR((MID(B6,(FIND("F",B6)-4),4))*1,(MID(B6,(FIND("A",B6)+1),3))*1))

This seems to work for the data that you gave.
Regards
Harry

Hello again Donald,
On the matter of another way to extract the numbers, this is a way to do it.  Haven't quite figured out yet how to deal with the  'different duplicates'.  If the characters in the first part of the string don't always total 7, you would have to determine that by finding the position of the -.  If there is a limit to the number of characters to the right of - the number of rows in the formula can be changed to suit.

=1*MID(RIGHT(B6,LEN(B6)-7),MATCH(TRUE,ISNUMBER(1*MID(RIGHT(B6,LEN(B6)-7),ROW($1:$50),1)),0),COUNT(1*MID(RIGHT(B6,LEN(B6)-7),ROW($1:$50),1)))

This has to be entered as an array formula (CTRL-SHIFT-ENTER)

Regards
Harry

Hi Donald,
A slightly less messy way to do it using your original concept.
=IFERROR(IF(B6="",C5,MID(B6,(FIND("-",B6)+2),3+(MID(B6,(FIND("-",B6)+2),3)*1=20))),IFERROR((MID(B6,(FIND("F",B6)-4),4))*1,(MID(B6,(FIND("A",B6)+1),3))*1))

Regards
Harry

Harry thanks for posting and helping,

I had way to many things on my mind to properly ask my question and left out information.    Here is what I was trying to do.   the short data table on the left is about 80 lines long and I only took out about 5 or 6 of them for my question. 

That data is pulled from the Web and cant be changed, but all I needed was the last set of numbers to use in a vlookup table for the rest of the data, or so I thought..  I set up my lookup tables first and limited myself to numeric values, not realizing that I could use both numbers and char's once I 'woke up' changed my lookup tables I reevaluated my approach and did this instead.

=IF(B6="",C5,(TRIM(MID(RIGHT(B6,10),FIND("-",RIGHT(B6,10))+1,9))))   

The values right of the last - in the data is what I wanted, but I was trying to create a formula to evaluate all the different data values I might come across, like the "F" or the "A" or the 20 O, 20 S and build a monster for something simple when you look at it in a different way.   that is what I love about excel.   after I posted my change on another site, I was given even a different way to accomplish it again.  like this

=IF(B6="",C5,TRIM(RIGHT(SUBSTITUTE(B6,"-",REPT(" ",99)),99)))

so I don't have to guess how many spaces to move left in order to capture the "-" and then more right stripping out the data. 

Again sorry for not providing all the data in my first question, and thank you for your help. 

Don 

Not a problem Donald,
We all have these 'senior moments' from time to time and make things more complicated than they need to be.
Harry