How to Search for Asterisk in Excel: 6 Best Methods

When you’re working in large Excel worksheets created from external databases, you’ll find various anomalies and funny characters in between data. One such character is the asterisk (*) and it can make data analysis challenging. Read this guide to learn the techniques to search for asterisk in Excel.

Microsoft Excel is the most popular spreadsheet app for database creation, data analysis, and data visualization to extract valuable insight from bulk data. However, during the development of databases on Excel or importing databases from another source, some data may get corrupted.

Sometimes, Excel may show this corrupted data in funny characters like asterisks, exclamation marks, question marks, and so on. To get rid of the asterisk and other unwanted characters in your Excel database, try any or all of the methods outlined here.

The Challenge With Asterisk Search in Excel

Asterisk function as a wildcard character in Excel. If you put the asterisk mark in the Excel Find and Replace tool, it’ll fetch every text or number that has more than one character. You can also use an asterisk as a wild card to match characters in an Excel worksheet.

For example, you can enter T* into the Find and Replace tool to locate all the texts in the database that starts with the character T. Thus, you can’t just search asterisk to look for it. The search will select all the cells that contain any values one at a time.

Thus, simple Find and Replace doesn’t work with the asterisk character. If you need to find all asterisks in an Excel worksheet, try any of the methods here. As a bonus, these methods also show you how to replace asterisks so that you can cleanse your Excel database for further data analysis work.            

How to Search for Asterisk in Excel

Depending on the Excel worksheet at hand, the strategy to search for Asterisk in Excel changes. For instance, if you’re simply looking for the locations of asterisks, the special asterisk search technique is convenient. 

Contrarily, if you need to search and replace unwanted asterisks in a large Excel worksheet, you need to use specialized formulas. Read on to find all the methods you’ll ever need to find and remove asterisks from Excel.  

Method 1: Using Tilde During a Search

The best and quickest way to look for asterisks in tables, databases, and texts of Excel is by using the tilde (~) wildcard. You can type a tilde by pressing Shift and then pressing the Tilde key on a Windows keyboard. The Tilde key is just below the Esc key.  

Here are the steps you should know:

1. Open an Excel worksheet that contains asterisks.
2. Press Ctrl + F to call the Find and Replace tool.

3. Alternatively, you can go to the Editing commands group on the Home tab and click Find & Select to get the Find and Replace tool.

4. In the Find tab of the Find and Replace tool, type ~* to search for asterisks.
5. Click Find Next to cycle the cell selector via each cell containing an asterisk sign one at a time.
6. Alternatively, you can click Find All to populate a table of all cells containing the asterisk character. The table shows data points like workbook names, sheet names, cell addresses, and exact cell values.  

Method 2: Using A Custom Formula

Suppose you need to find out if a cell contains an asterisk or not and populate TRUE/FALSE on the basis of the presence of this character. Then, you can use this simple nested formula to get TRUE or FALSE in the cell just beside the target cell. Here’s how it’s done:

1. Select any cell just to the right of the target dataset as shown in the picture.
2. Now, enter an equal sign (=) and copy and paste the following formula into the cell where you want TRUE/FALSE values:

=ISNUMBER(SEARCH("~*",B2))

3. Hit Enter and the cell will either show TRUE if the reference cell has one or more asterisks.

4. Contrarily, the cell will show FALSE if the reference cell doesn’t contain any asterisk.
5. Copy and paste the formula downwards in the same column to get results for other rows of data.  

Method 3: Using Find and Replace Command

Let’s imagine, you’ve got a data table of famous inventors and their innovations linked by asterisks due to some data import script mistake. Now, you need to convert those data to comma-separated values so that you can save the Excel file in the CSV file format. Here’s what you must try:

1. Open the worksheet in question and select the dataset that you need to convert to comma-separated values from asterisk-separated values.

2. Hit Ctrl + F to get the Find and Replace tool.
3. Select the Replace tab.
4. Type in ~* in the Find what field and the character comma (,) in the Replace with field.
5. Click Replace All to instantly convert the entire dataset into a comma-separated entry. 

Method 4: Using the Find Function

You can also use the Excel FIND function to search for asterisk in Excel without using the tilde wildcard. Here’s how it’s done:

1. Select any cell where you need to find out whether an asterisk is present in the target cell.
2. Type an equal (=) operator and copy and paste this formula:

=(FIND("*",B2))

3. Here, the FIND function will fetch how many times an asterisk appears in the cell.

4. If there are no asterisks, then you’ll see the #VALUE! error.
5. To eliminate the error so the worksheet looks beautiful, you can wrap the above formula using the ISNUMBER function as shown below:
6. Now, copy and paste the formula across the entire column to detect cells in datasets that contain one or more asterisks. 
7. Cells that contain asterisks will show the value TRUE. And, those that don’t, will show the value FALSE.     

Method 5: Using a Custom VBA Code

For example, you’ve got an Excel database containing thousands of entries of customers’ names who subscribed to your website or mobile app. Now, due to some programming bug, all the customer names and their account data got separated by asterisks rather than regular space or underscore. 

Now, you need to introduce space between customer names, email addresses, home addresses, choice of genre, and more. How do you do that in a single click? You can use the following VBA code that finds and replaces asterisks in a flash:

1. Go to the Excel worksheet that contains thousands of texts separated by asterisks.
2. Right-click on the worksheet tab or worksheet name at the bottom of the app.

3. Select View Code on the context menu that shows up.
4. Copy and paste the following code into the VBA editor:

Sub M_snb()
ActiveSheet.Cells.Replace "~*", "-"
End Sub

5. Click the Run Sub icon or press F5 to execute the VBA script.

6. Excel will replace asterisks with hyphens. You can change the hyphen to any other character or text you like. 

Method 6: Using the SUBSTITUTE Function

Let’s say, you want to search for asterisks in an Excel worksheet database and immediately replace asterisks with another character or text. That’s possible if you use the SUBSTITUTE function by following the steps outlined below: 

1. Access the Excel dataset where you need to find and replace the asterisk with any other text like the hyphen.
2. Besides one row of data, copy and paste the following function. Ensure, you edit the SUBSTITUTE text reference correctly.

=SUBSTITUTE(I3,"*","-")

3. Hit Enter and you get texts separated with hyphens or any other character you used.

4. Don’t forget to replace the cell reference I3 with the exact target cell address according to your Excel dataset or table or database.
5. Copy and paste the formula across the column to apply the function to all the rows automatically.

Conclusions

So far, you explored six different ways that you can utilize in your search for asterisk in Excel workbooks. For personal and casual tasks, you should use a tilde before the asterisk in the Find and Replace tool to locate all the asterisks one by one.

If your Excel workbook is huge and contains thousands of entries, you may want to apply the Find function, the SUBSTITUTE function, or a simple VBA script.

Go ahead and give the above methods a try using your own Excel datasets. Don’t forget to leave a comment below about your experience. Also, if you know other ways to search for asterisks in Excel, leave a note below.