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

Asked By Donald Ross on 23-Nov-13 07:57 AM
=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
Harry Boughen replied to Donald Ross on 23-Nov-13 03:19 PM
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
Harry Boughen replied to Donald Ross on 23-Nov-13 03:53 PM
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
Harry Boughen replied to Donald Ross on 23-Nov-13 04:24 PM
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
Donald Ross replied to Harry Boughen on 24-Nov-13 10:30 AM
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 

Harry Boughen replied to Donald Ross on 24-Nov-13 01:58 PM
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