Nate Oliver's blog

Good evening. I misread an Excel question in the forums the other night, I thought they wanted to extract odd and even numbers from an Excel cell - which isn't what they wanted. It happens when you speed-read through hundreds of questions...

Nevertheless, you can chomp this kind of data with Excel. I wrote a couple of UDFs to do this, e.g.,

Public Function odd_Num(ByRef strIn As String) As String
Dim b() As Byte, varArr() As Variant
Dim i As Long, j As Long
Let b = strIn
ReDim varArr(1 To UBound(b))
For i = LBound(b) To UBound(b) Step 2
    If b(i) \ 2 <> b(i) / 2 Then
        Let j = j + 1
        Let varArr(j) = ChrW$(b(i))
    End If
Next
ReDim Preserve varArr(1 To j)
Let odd_Num = Join$(varArr, vbNullString)
End Function 

Public Function even_Num(ByRef strIn As String) As String
Dim b() As Byte, varArr() As Variant
Dim i As Long, j As Long
Let b = strIn
ReDim varArr(1 To UBound(b))
For i = LBound(b) To UBound(b) Step 2
    If b(i) \ 2 = b(i) / 2 Then
        Let j = j + 1
        Let varArr(j) = ChrW$(b(i))
    End If
Next
ReDim Preserve varArr(1 To j)
Let even_Num = Join$(varArr, vbNullString)
End Function 

Not so fast, Nate Oliver, you might ask? You'd be quite right, literally. I'm working too hard with Function calls to make this happen. So, let's streamline this. We'll go from Byte Array to Byte Array, e.g.,

Public Function odd_Num2(ByRef strIn As String) As String
Dim b() As Byte, b2() As Byte
Dim i As Long, j As Long
Let b = strIn
ReDim b2(LBound(b) To UBound(b))
ReDim varArr(1 To UBound(b))
For i = LBound(b) To UBound(b) Step 2
    If b(i) \ 2 <> b(i) / 2 Then
        Let b2(j) = b(i)
        Let j = j + 2
    End If
Next
ReDim Preserve b2(LBound(b) To (j - 2))
Let odd_Num2 = b2
End Function

Public Function even_Num2(ByRef strIn As String) As String
Dim b() As Byte, b2() As Byte
Dim i As Long, j As Long
Let b = strIn
ReDim b2(LBound(b) To UBound(b))
For i = LBound(b) To UBound(b) Step 2
    If b(i) \ 2 = b(i) / 2 Then
        Let b2(j) = b(i)
        Let j = j + 2
    End If
Next
ReDim Preserve b2(LBound(b) To (j - 2))
Let even_Num2 = b2
End Function

Now life becomes easier, should one want to do this. For example, to retrieve the odd numbers out of A1, you would call the UDF as such:

=odd_num2(A1)

And the even numbers in A1?

=even_num2(A1)

And, of course, you can rename the UDFs to your liking. There's no error-trapping built into any of this, and it's only been somewhat tested against Integers. It's not a free-for-all solution, it's an approach to a problem.

Otherwise, have a great night!
Nate