Table of Contents
To search for an asterisk in a cell in Excel, press CTRL+F to open the Find and Replace dialogue box. Then type an asterisk (*) into the Find What field, and click Find All. This will search the entire sheet and list all the cells that contain an asterisk.
You can use the following formula to check if a given cell in Excel contains an asterisk anywhere in the cell:
=IF(ISNUMBER(SEARCH("~*", A2)), "Yes", "No")
This particular formula checks if cell A2 contains an asterisk and returns “Yes” if it does.
Otherwise, it returns “No.”
Note: The tilde symbol (~) in Excel indicates that the next character is a literal.
The following example shows how to use this formula in practice.
Example: Search for Asterisk in Cell in Excel
Suppose we have the following dataset that contains information about various basketball players:
An asterisk at the end of the team name indicates that the player is an All-Star.
Suppose we would like to search each team name in column A to determine if each player is an All-Star or not.
To do so, we can type the following formula into cell C2:
=IF(ISNUMBER(SEARCH("~*", A2)), "Yes", "No")
We can then click and drag this formula down to each remaining cell in column C:
The formula returns “Yes” if the team name contains an asterisk.
Otherwise, it returns “No” if no asterisk is found.
- Mavs* does contain an asterisk.
- Nets does not contain an asterisk.
- Warriors does not contain an asterisk.
And so on.
Note that if you’d like to return values other than “Yes” and “No”, simply replace these values in the formula with whatever values you’d like.
Also note that you can use the following formula to return TRUE or FALSE to indicate whether a cell contains an asterisk or not:
=ISNUMBER(SEARCH("~*", A2))
The following screenshot shows how to use this formula in practice:
The formula returns TRUE or FALSE to indicate whether each team name contains an asterisk or not.