home Computers Escaping The Asterisk (*) in Excel

Escaping The Asterisk (*) in Excel

I had a formula the other day that needed to see if an asterisk – * was present in a cell. So the formula included a comparison for “*” in the syntax. Anyhow, turns out some formulas (not all) will treat the * as a wildcard and match it to any number of letters or numbers. If you are a programmer, like myself, then you are used to escaping letters with a backslash (\). However, Excel does not use this and the proper escape is a tilde (~).

I was a bit surprised how difficult searching the internet for this solution was, so I’m posting it here too. Hopefully I have good enough keywords and explanation that it will be easier for others like me to find.

So my formula that was giving me issues was a SUMIF. It looked like this:


=SUMIF(A1:A500,"=*",B1:B500)

My intention was to add up all the B column values that had a * in the A column, however it was adding up all the B column fields regardless. Once I changed it to the formula below, it worked just fine.


=SUMIF(A1:A500,"=~*",B1:B500)

Leave a Reply