When part numbers aren't all numbers...

Context

One of the sites I subscribe to is Excel Ribbon Tips (excelribbon.tips.net), authored by Alan Wyatt.  The information is useful for Excel users and VBA/Excel programmers.  He also provides tips in Word...I'm just not in that mode right now.

In most of his weekly tip sheets there is a call for help from a reader, someone who has a problem they are trying to solve.  The following is the one of the answers I provided.

The Question

One of Alan's subscribers had asked for help in identifying part numbers that have alphabetic characters in them.  

The Answer(s)

One thought, the one I submitted, was to use a function to identify an entry that had one on or more of the 26 letters we have in the English alphabet.  It could be used either as part of a Conditional Formatting statement, in a column to flag the cell next to it, or  called in a VBA subroutine or function.

Function HasAlpha(ByRef PartNo As String)

    Dim Ch_Ctr

    HasAlpha = False

    For Ch_Ctr = 1 To Len(PartNo)

        If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", UCase(Mid(PartNo, Ch_Ctr, 1))) > 0 Then

            HasAlpha = True

            Exit Function

        End If

    Next Ch_Ctr

End Function

An alternative would be to make certain the part number had only allowable characters, meaning it could now be more specific.

Function IsValidPartNumberString(ByRef PartNo As String) as Boolean

    Dim Ch_Ctr

    IsValidPartNumberString= True

    For Ch_Ctr = 1 To Len(PartNo)

        If InStr(1, "1234567890 -/()", UCase(Mid(PartNo, Ch_Ctr, 1))) = 0 Then

            HasAlpha = False

            Exit Function

        End If

    Next Ch_Ctr

End Function

For other ideas you will need to visit Allen's site.