Wildcard '*' causing a hassle!

I have used a macro to remove duplicate items from a list in excel. This left (amongst thousands of others) the following two pieces of text in 2 rows;

BALLOON & INFLATION BULB: HERNIA REPAIR;KIDNEY DIST;(TYCO #999)
BALLOON & INFLATION BULB:* HERNIA REPAIR;KIDNEY DIST;(TYCO #999)

It left these because of the * (I am assuming)

The problem I have now is - if I run a =sumif() across this data, the '*' acts as a wildcard and treats the two rows as being identical. We are unable to edit these descriptions and the spreadsheet is built using macros and many other similar instances can easily happen.

Qu. 1 is there a way of negating the '*' in the setting of Excel?
Qu. 2 is there another way to run the =sumif so that these lines are CORRECTLY treated as being different?

thanks for your help

regards
Quinn

In your SUMIF you can precede the * with a tilde (~), which instructs Excel to treat is as a literal value, rather than a wildcard.

Thanks for this - we are unable to edit the individual rows in which the wildcard appears which I think would need tobe done for this fix?????

Anyway - I managed to get around it by beginning the macro with changing the format of the cells to TEXT and then it treated the * literally so that seems to have worked. Thanks for the advice though - didnt know about the ~ and will definitely use in the future.

Perhaps you can help with my NEW post! :-)

You shouldn't need to edit the rows, just the macro that's doing the searching to replace an asterisks in the search term with "~*" before you search.