Counting fields with an * in Excel--how?

Factual Questions
1

I have an Excel spreadsheet which contains a column of names of the spouses of the people in our database. Now, the software which we use originally didn’t have the ability to allow us to mark whether the spouse was deceased. So, many years ago, it was decided that deceased spouses’ names would be marked with a non-letter character.

Unfortunately for me, that non-letter character was an asterisk–which of course is the wild-card character in MS Excel. Now I’m working on a statistical analysis of our database, and one of the things I want to do is to have a column which marks whether the spouse is deceased or not. If another character like an ! had been used, I’d probably just do a formula like

=IF(A2="!",“Spouse Deceased”,"")

…but obviously I can’t do that. I can’t find-and-replace asterisks with another character, either.

So: is there a way to get around this in Excel, or am I going to have to use another program to count up our dead?

2

You can try escaping the character. Two ways:

Use the chr (or is it char) function and put in the ASCII code for *

OR

Enclose the it as such “’*’”

(Note the single quote within the double quote)

3

Hmmm…this doesn’t seem to be working but I might not be using the right syntax. I have a formula as follows:

=IF(A2=CHAR(42),“Spouse Deceased”,"")

…where CHAR(42) is the ANSI code for the * sign. In their infinite wisdom our forerunners put the * sign after the first name of the deceased spouse, i.e. John* S. Smith. So I need a syntax which will look for an * sign anywhere within the field. That’s where the holdup is thus far.

4

If your worksheet happened to contain data in cells A1:A9, your formula would be:

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

The first and third asterisks act as wildcards. The combined ~* actually tells Excel it is searching for an asterisk. Change the A1:A9 to suit your data range needs.

5

That’s cracked it, Mullinator! Thanks a million.

6

Sorry, my last reply was based on completely misreading your needs. To determine by cell if it contains an asterisk, do the following (I assume your data starts in cell A1):

In cell B1, put:
=IF((ISERROR((SEARCH("*~**",A1))>0)),"",“Spouse Deceased”)

The SEARCH function looks in cell A1 for an asterisk as an actual character. This would then return a number if there is an asterisk and a #value! if one is not found. Add in the ISERROR function which converts a number to False (since there is a real result therefore it is false that this is an error) and #value! to True. Then, the IF statement says that if the end result is a TRUE statement, leave the cell blank since there is no dead spouse. If FALSE, return Spouse Deceased.

7

Actually, the first formula you gave was close enough for government work–with a bit of tweaking, it returned “0” if the cell did not contain an asterisk, and “1” if it contained one.