How to type and find an asterisk in Excel
Type Shift + 8 in a cell to enter an asterisk. In formulas it means multiply, and in COUNTIF, VLOOKUP, MATCH and Find & Replace it's a wildcard. To match a real asterisk there, put a tilde before it: ~*.
The three jobs of * in Excel
| Where | What * does | Example |
|---|---|---|
| Typed into a cell | Just text | * shows an asterisk |
| In a formula | Multiplication | =A1*B1 |
| In lookup and criteria functions | Wildcard: "any number of characters" | =COUNTIF(A:A,"apple*") counts cells starting with "apple" |
The wildcard behaviour applies to criteria and lookup functions, and to Find & Replace and filters. Our guide to Excel wildcards lists which functions take them, which don't, and how to get "contains" matching into FILTER and IF.
Matching a real asterisk: use ~*
A tilde tells Excel the next character is literal.
| You want to | Formula |
|---|---|
| Count cells that contain an asterisk anywhere | =COUNTIF(A:A,"*~**") |
| Count cells that are exactly * | =COUNTIF(A:A,"~*") |
Look up a code like AB*12 |
=VLOOKUP("AB~*12",A:B,2,FALSE) |
In "*~**", the first and last * are wildcards and the ~* in the middle is the literal asterisk.
Removing asterisks
- Find & Replace: press Ctrl + H, put
~*in Find what, leave Replace with empty, and click Replace All. Searching for a bare * would match every cell and wipe them all. - With a formula:
=SUBSTITUTE(A1,"*","")removes every asterisk from A1. SUBSTITUTE doesn't use wildcards, so no tilde is needed.
Star ratings with REPT
=REPT("✱",B2) repeats a character as many times as the number in B2, which makes a quick text rating column. Swap in ★ or plain * to taste.
The * in number formats
In a custom number format, * means "repeat the next character to fill the cell". The built-in Accounting format uses * (asterisk, space) to push the currency sign to the left edge. If you see * in Format Cells → Custom, that's what it's doing. It's not a symbol that will appear in the cell.