Excel: How To: Function to identify cells with "*" [RESOLVED]

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 Functions

I regularly receive a worksheet that has some cells with "*" as the contents of the cell. I need to programmatically find the address of these cells with a function. I have tried the "Match" and "Countif" functions, but they both seem to consider the "*" as a wildcard!

=MATCH("*",A7:E7,0)
=MATCH(CHAR(CODE("*")),A7:E7,0)
=COUNTIF(A7:E7,"*")

Is there some way to turn off the wildcard action of "*"? Is there a simple function to find an occurrence of "*" as the total contents of the cell in a range of cells (row)?

[medison]

I would have expected the first Match function to work. For the CountIf try adding a tilde:

=COUNTIF(A1:A10,"~*")


HTH

[Webtest]

I finally bit the bullet and wrote a short function for this one. My 'cell of interest' only contains a single asterisk character. Here is how I solved it:

(Option Explicit)
'=======================================================================================
'Function to determine if the first cell in a range contains only an asterisk
'
'Pass Range Object which points to the cell to test
'Return Boolean ... False if NOT Asterisk,  True if IS ASTERISK
'
Private Function IsAsterisk(aRange As Range) As Boolean
    
    'Set the Function Return to FALSE = NOT an Asterisk
    IsAsterisk = False
    'Fetch the contents of the cell
With aRange
    If IsNumeric(.Value) Or IsEmpty(.Value) Then
        'TEST TEST TEST TEST
        'MsgBox "Cell is NUMERIC or EMPTY"
        'END TEST
    Else
        'Check to see if this is a single Asterisk character
        If Len(.Value) = 1 And Asc(.Value) = 42 Then
            'Yes, this IS an ASTERISK - Set the Return Parameter
            IsAsterisk = True
            'TEST TEST TEST TEST
            'MsgBox "This is an asterisk!"
            'END TEST
        End If
    End If
End With

End Function