Excel Formula Challenge: Even or Odd License Plates

I was able to do it in Excel 2019, but had to use 4 sets of helper cells per plate. So, it’s not pretty, but it works!

Helper #1 – text to columns to break the letters and numbers apart.

Helper #2 – determine if helper #1 is a number and if it is was it even or odd.

Helper #3 – determine if helper #2 was the last digit.

Helper #4 – is the last digit even or odd.

Answer – if statement using helper #4

I know it is not an “elegant” solution but it works 🙂

=IFERROR(IF(ISODD(MID([@[Plate Number]];MAX(IF(ISNUMBER(VALUE(MID(A5;ROW(INDIRECT(“1:” & LEN(A5)));1)));ROW(INDIRECT(“1:” & LEN(A5)))));1));”Odd”;”Even”);”Odd”)

my formula for the license plate challenge is the following formula

=IF(ISEVEN(IFS(ISNUMBER(VALUE(RIGHT(A11,1))),VALUE(RIGHT(A11,1)),ISNUMBER(VALUE(MID(A11,LEN(A11)-1,1))),VALUE(MID(A11,LEN(A11)-1,1)),ISNUMBER(VALUE(MID(A11,LEN(A11)-2,1))),VALUE(MID(A11,LEN(A11)-2,1)),ISNUMBER(VALUE(MID(A11,LEN(A11)-3,1))),VALUE(MID(A11,LEN(A11)-3,1)),ISNUMBER(VALUE(MID(A11,LEN(A11)-4,1))),VALUE(MID(A11,LEN(A11)-4,1)),ISNUMBER(VALUE(MID(A11,LEN(A11)-5,1))),VALUE(MID(A11,LEN(A11)-5,1)),ISNUMBER(VALUE(MID(A11,LEN(A11)-6,1))),VALUE(MID(A11,LEN(A11)-6,1)),TRUE,1)),”Even”,”Odd”)

Jon,

I can offer the following solution.

Start with a helper column (C) with this formula:
=IFERROR(VALUE(RIGHT(IF(SUM(LEN(A5)-LEN(SUBSTITUTE(A5, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))>0, SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(–MID(A5,ROW(INDIRECT(“$1:$”&LEN(A5))),1))* ROW(INDIRECT(“$1:$”&LEN(A5))),0), ROW(INDIRECT(“$1:$”&LEN(A5))))+1,1) * 10^ROW(INDIRECT(“$1:$”&LEN(A5)))/10),””),1)),””)

Then in the table use the formula:
=SWITCH(C5,0,”Even”,1,”Odd”,2,”Even”,3,”Odd”,4,”Even”,5,”Odd”,6,”Even”,7,”Odd”,8,”Even”,9,”Odd”,””,”Odd”)

My normal approach would be to use a function which allows for testing and updating in one place.

Jon,

Here is an approach using VBA functions. Use =fPlateDay([@[Plate Number]]) in table.

Public Function fPlateDay(platenumber) As String
‘Determine if car entry is on odd or even days of month
Dim plateno As Variant
plateno = fExtractNumeric(platenumber)

If Len(plateno) > 0 Then
plateno = Right(Val(plateno), 1)
Select Case plateno
Case 0, 2, 4, 6, 8
fPlateDay = “Even”
Case 1, 3, 5, 7, 9
fPlateDay = “Odd”
End Select
Else
fPlateDay = “Odd”
End If
End Function

Public Function fExtractNumeric(strInput) As String
‘ Returns the numeric characters within a string in
‘ sequence in which they are found within the string
Dim strResult As String, strCh As String
Dim intI As Integer
If Not IsNull(strInput) Then
For intI = 1 To Len(strInput)
strCh = Mid(strInput, intI, 1)
Select Case strCh
Case “0” To “9”
strResult = strResult & strCh
Case Else
End Select
Next intI
End If
fExtractNumeric = strResult
End Function

=LET(num, CODE(MID(UPPER(tblChallenge[@[Plate Number]]),SEQUENCE(,LEN(tblChallenge[@[Plate Number]]),1,1),1)),
onlynum,FILTER(num,num<65,1),
IF(ISEVEN(INDEX(onlynum,1,COUNT(onlynum))),"Even","Odd"))

I split the plate into an array then filter out all the letters, then I just check if the last character in the remaining array is Even, if it is not it means it is Odd or it would be odd due to empty array on the filter if there were no numbers.

Recursive lambda inside a LET() using fixed-point combinators.

=LET(
p,[@[Plate Number]],
x,LEN(p),
r,LAMBDA(G,x,p,IF(x=0,1,IF(ISNUMBER(VALUE(MID(p,x,1))),MID(p,x,1),G(G,x-1,p)))),
IF(ISODD(r(r,x,p)),”Odd”,”Even”)
)

Description of my solution:

First I split the License Plate into columns of 1 character:
MID(tblChallenge[@[Plate Number]], SEQUENCE(1,LEN(tblChallenge[@[Plate Number]])), 1)

Next, I use XMATCH to look up a list of numbers 0 – 9 in the spilled array, starting from the end.
Add Zero to convert a “text number” to an actual number:
XMATCH({0,1,2,3,4,5,6,7,8,9},O5#+0,0,-1)

If there is no number in the License Plate, this will give an error.
Used IFERROR to return 0 in this case.
IFERROR(XMATCH({0,1,2,3,4,5,6,7,8,9},O5#+0,0,-1),0)

Then wrap this formula in a MAX formula, to return the highest column number, that
contains a number.
MAX(IFERROR(XMATCH({0,1,2,3,4,5,6,7,8,9},O5#+0,0,-1),0))

This is the formula in column L, and now I now in which position the last number is.
0 is returned, if no number is found.

In column M, I then use Index to return the number from the column that we just found above.
INDEX(O5#,,L5)

I then wrap this in ISEVEN to see, if the number is even or not.
ISEVEN(INDEX(O5#,,L5))

And now an IF Formula. If the number is even, then return “Even”, otherwise return “Odd”.
IF(ISEVEN(INDEX(O5#,,L5)+0)=TRUE,”Even”,”Odd”)

Almost there! :-), Now, in column B another IF formula. If there is no number in the plate,
then Col L contains 0. Then return “Odd”, otherwise return the Even/Odd answer from above.
IF(L5=0,”Odd”,M5)

To address this specific challenge in my own way, I created one macro to copy license plates from the ‘license plate generator’ tab to the ‘challenge’ tab and then used the Data ‘text to column’ to separate the digits of the license plate. To run the macro, I created nd use the short cut CTRL r

Next, I added another column to the table with a formula to find the last digit in the license plate number.
=IFERROR(INDEX(D5:I5,MATCH(9.99999999999999E+307,D5:I5)),” “)

Lastly, in column B, I used this formula to determine the plate to be eligible on an ODD day or an EVEN day
=IFERROR(IF((ISODD(J5))=TRUE,”ODD”,”EVEN”),”ODD”)

With what I have done, the table size in now A4 to J54.

Works great.

bulky and notvery elegant, but works
=IFERROR(IF(ISODD(VALUE(MID([@[Plate Number]];LEN([@[Plate Number]])+1-MATCH(TRUE;ISNUMBER(VALUE(MID([@[Plate Number]];SEQUENCE(LEN([@[Plate Number]]);1;LEN([@[Plate Number]]);-1);1)));0);1)));”Odd”;”Even”);”Odd”)
sequence creates a reversed matrix of true/false for numeric characters in the string, match returns the position of the first true value, isodd/mid nested in if/iferror return the odd/even result

For XL365 or XL2021 (or later)…

=IF(ISODD(LOOKUP(10,0+MID(1&A1,ROW($1:$9),1))),”Odd”,”Even”)

For any version of Excel…

=IF(ISODD(LOOKUP(10,0+MID(1&A1,ROW($1:$9),1))),”Odd”,”Even”)

Some old mixed with some new.
=IF(ISEVEN(IFERROR(LOOKUP(99^99;–MID([@[Plate Number]];SEQUENCE(LEN([@[Plate Number]]));1));-1));”Even”;”Odd”)
Though actually the if is not required. TRUE/FALSE would be enough for me.

Power Query (disappointedly complex)
let
Source = Excel.CurrentWorkbook(){[Name=”tblChallenge”]}[Content],
IsEven = Table.AddColumn(Source, “Iseven”, each try Number.IsEven(List.Last(List.RemoveNulls(List.Transform(Text.ToList([Plate Number]), each try Number.FromText(_) otherwise null)))) otherwise false, Logical.Type)
in
IsEven

Wrote solution in VBA. Added date and in column D added license plates that can enter the park on that date.

Sub LicenseOddEven()

Dim License As String
Dim i As Integer
Dim j As Integer
Dim LastRow As Integer
Dim LastNum As Integer
Dim k As Integer
Dim StrLen As Integer
Dim TheDay As Integer

LastRow = Range(“A5”).End(xlDown).Row
LastNum = 0
TheDay = (Day(Date)) Mod 2
cells(2, 2).ClearContents
cells(2, 2).Value = Date
cells(2, 2).NumberFormat = “mm/dd/yyyy”

For k = 5 To LastRow
cells(k, 2).ClearContents
cells(k, 4).ClearContents
Next k

For j = 5 To LastRow
License = cells(j, 1).Value
For i = 1 To Len(License)
If IsNumeric(Mid(License, i, 1)) Then
LastNum = Mid(License, i, 1)
End If
Next i
‘MsgBox (License & ” ” & LastNum)
If LastNum = 0 Then
cells(j, 2).Value = “Odd”
ElseIf LastNum Mod 2 Then
cells(j, 2).Value = “Odd”
Else
cells(j, 2).Value = “Even”
End If
LastNum = 0
Next j

i = 5
For k = 5 To LastRow
If TheDay = 0 And cells(k, 2).Value = “Even” Then
cells(i, 4).Value = cells(k, 1).Value
i = i + 1
End If
If TheDay = 1 And cells(k, 2).Value = “Odd” Then
cells(i, 4).Value = cells(k, 1).Value
i = i + 1
End If

Next k

End Sub

Hello,
It was a great challenge! I did it with the following VBA code:

Option Explicit

Sub odd_or_even()

Dim plate_number As String
Dim checked_sign As Variant
Dim sign_position As Long
Dim row_number As Long

For row_number = 5 To 54

plate_number = Cells(row_number, 1).Value
sign_position = Len(plate_number)

For sign_position = Len(plate_number) To 1 Step -1

checked_sign = Mid(plate_number, sign_position, 1)

If IsNumeric(checked_sign) = True Then

If checked_sign Mod 2 = 0 Then
Cells(row_number, 2).Value = “EVEN”
Else
Cells(row_number, 2).Value = “ODD”
End If

Exit For
Else

End If

Cells(row_number, 2).Value = “ODD”
Next sign_position

Next row_number

End Sub

Further to my comment yesterday, the problem can be solved using named formulas instead of
helper columns D to S as follows:
str_org =$A5
n_char =LEN(str_org)
seq2_8 ={2,3,4,5,6,7,8}
ind_char =MID(str_org,n_char-seq2_8+2,1)
odd_or_even =IF(ISNUMBER(VALUE(ind_char)),IF(ISEVEN(VALUE(ind_char)),”EVEN”,”ODD”),”TEXT”)
odd_last =IFERROR(n_char-MATCH(“ODD”,odd_or_even,0)+1,”TEXT”)
even_last =IFERROR(n_char-MATCH(“EVEN”,odd_or_even,0)+1,”TEXT”)

result =IF(AND(odd_last=”TEXT”,even_last=”TEXT”),”Odd”,IF(AND(ISNUMBER(odd_last),ISNUMBER(even_last)),IF(odd_last>even_last,”Odd”,”Even”),IF(odd_last=”TEXT”,”Even”,”Odd”)))

=result to be entered in cell B5

Late to the party, but here’s my VBA solution

Option Explicit

Function LPlate(strPlate As String)
Dim intAns As Integer
Dim IntStep As Integer
Dim intChr As Integer

intAns = 1

For IntStep = 1 To Len(strPlate)
intChr = Asc(Mid(strPlate, IntStep, 1))
If intChr >= 48 And intChr <= 57 Then intAns = intChr
Next IntStep

LPlate = "Odd"

If intAns Mod 2 = 0 Then LPlate = "Even"

End Function

Copied Plates to helper, did “text to col” fixed, length 1 markers.
Repeated use of isnumber in repeated nested if statements
=if(isnumber(lastcol),lastcol,if(isnumber(last-1col), last-1col,if(isnumber(last-2col,….,999))))..)
Then used =if(isodd(resultcol),”ODD,”EVEN”)

Excellent challenge! Can’t wait to challenge myself!