vendredi 18 novembre 2016

How to check for number and text in a cell using IF OR

I am stuck trying to get a formula to function correctly. What my goal is is to check a cell (D8) for either A) a specific number in an ID number to determine the sex of the student or B) the sex of a foreign student by means of a post-fix on the passport number (-F/-M).

Cell D8 is used to capture the ID/Passport number and D9 is used to populate M/F from the results in D8.

Currently this is working fine on South African ID's using:

=IF(--MID(D8,7,1)<5,"F","M")

Now I need to add another condition that will check if the cell contains -F and if it does D9 will be F, otherwise it will be M, using:

ISNUMBER(SEARCH("-F",D8))

The problem I simply cannot seem to fix, maybe as it's urgent ;) is to combine these to conditions on the input of D8.

Aucun commentaire:

Enregistrer un commentaire