CODEWITHSUNDEEP
excelexcel-formulamultiple-conditions
Andre van Rensburg
Andre van Rensburg

Reputation: 161

multile if and or conditions in excel formula

I need to check If cell D8's value:

Starts with a number or a letter, ends with -f or -m

Then if the 7th digit in the sequence is less than 5, but only if it does in fact start with a number, if it start with a letter it must check the ending -f or - m part...

Here is what I have:

=IF(ISNUMBER(VALUE(LEFT(D8,1), IF(--MID(D8,7,1)<5,"F","M"),IF(IFERROR(FIND("-F",D8),FALSE),"F","M"))

EDIT: If start with number, then check if digit 7 is < 5, if start with letter, see if value ends with -f or -m

if digit 7 < 5 result = F else M if ends with -f result = F else M

both ID and Passport are captured in D8, ID are numeric only, passports starts with letters.

Upvotes: 0

Views: 68

Answers (2)

Hambone
Hambone

Reputation: 16377

If you're open to a UDF (using VBA), Regular expressions are really nice for this type of task because they are very scalable. You can surely do this from within Excel, but your functional already is very hard to read, and it's not that complex. If you start adding or changing conditions, the simple misplacement of a parenthese could derail everything.

A UDF is easy to read, write, debug and maintain. Here is an example, using the regular expression engine:

Function Checker(R As Range) As Boolean

  Dim result As Boolean
  Dim rxStartsLet As New RegExp
  Dim rxStartsNum As New RegExp
  Dim rxLessThan5 As New RegExp

  rxStartsLet.Pattern = "^[A-Za-z]"
  rxStartsNum.Pattern = "^[0-9]"
  rxLessThan5.Pattern = "[0-4]"

  If rxStartsNum.Test(R.Value2) Then
    result = rxLessThan5.Test(Mid(R.Value2, 7, 1))
  ElseIf rxStartsLet.Test(R.Value2) Then
    result = Right(R.Value2, 2) = "-F" Or Right(R.Value2, 2) = "-M"
  Else
    result = False
  End If

  Checker = result

End Function

And then your formula could reader:

=If(Checker(D8), "M", "F")

Note that the regex can determine "less than 5" without converting to a number -- in this case we just said the inclusive range of 0 to 4.

Upvotes: 1

L.Dutch
L.Dutch

Reputation: 966

Your formula doesn't work because you forgot some (). Try this one

=IF(ISNUMBER(VALUE(LEFT(D8,1))), IF(--MID(D8,7,1)<5,"F","M"),IF(IFERROR(FIND("-F",D8),FALSE),"F","M"))

Upvotes: 1

Related Questions