5 Easy Ways to Replace Asterisks in Excel Sheets

In the world of data management, Microsoft Excel stands out as a powerful tool, essential for various business tasks, from financial modeling to data analysis. However, when working with datasets, sometimes users face the challenge of dealing with asterisks (*) used for formatting, filtering, or as wildcards in formulas. Here’s a comprehensive guide on how to efficiently replace asterisks in Excel with different characters or text, tailored for enhancing your spreadsheet productivity.

Understanding Asterisks in Excel

Before diving into the methods of replacement, it’s vital to understand why asterisks might appear in your Excel sheets:

• Formatting: Asterisks often mark placeholder spaces or are used in specific formats.
• Wildcards: They are crucial in functions like FILTER, VLOOKUP, or conditional formatting to represent any number of characters.

Method 1: Using the Replace Feature

The simplest way to replace asterisks in Excel is by using the built-in Replace feature:

1. Open your Excel sheet containing asterisks.
2. Press Ctrl + H or go to the “Home” tab, click on “Find & Select,” and then choose “Replace.”
3. In the “Find what” box, type an asterisk (). Since Excel uses asterisks as wildcards, you need to search for the actual asterisk by entering ~.
4. In the “Replace with” field, enter the text or character you want to replace the asterisk with.
5. Click “Replace All” to replace all instances or “Replace” for specific ones.

✅ Note: The tilde (~) acts as an escape character for special characters like asterisks in Excel.

Method 2: Using Excel Functions

For more precise control over replacing asterisks, especially in complex datasets, Excel functions are your allies:

• SUBSTITUTE Function:

   =SUBSTITUTE(A1,””,“New Text”) 

  This function looks for “” in the cell A1 and replaces it with “New Text.”
• REPLACE Function: If you know the exact position of the asterisk:

   =REPLACE(A1,3,1,“#”) 

  Here, “*” in the third position is replaced with “#.”

Method 3: VBA Macro for Bulk Replacement

When dealing with large datasets, automation becomes necessary. Here’s how you can use VBA:

• Open the Visual Basic Editor by pressing Alt + F11.
• Insert a new module with Insert > Module.
• Paste this code:

  
  Sub ReplaceAsterisks()
      Dim ws As Worksheet
      Set ws = ActiveSheet
  
  ws.Cells.Replace What:="*", Replacement:="[Your Text Here]", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  
  
  End Sub
  

• Replace “[Your Text Here]” with your desired replacement text.
• Run the macro by pressing F5 or selecting Run > Run Sub/UserForm.

⚠️ Note: Always back up your data before running macros, as they can alter large portions of your spreadsheet in one go.

Method 4: Power Query for Data Cleansing

Power Query is another Excel feature ideal for data transformation tasks:

1. Select your range of cells with asterisks.
2. Go to the “Data” tab and click on “From Table/Range.”
3. In the Power Query Editor, select the column with asterisks, then:
   • Choose “Transform” > “Replace Values.”
   • Type “*” in the “Value to Find” field.
   • Enter your replacement text or character in “Replace With.”
   • Click “OK.”
4. Once done, load the query back into Excel.

Method 5: Custom Functions for Specific Needs

If your replacement needs are unique or conditional, consider creating custom Excel functions:

• Use VBA to define a function like:

  
  Public Function CustomReplace(cell As Range, char As String, replaceText As String) As String
      CustomReplace = WorksheetFunction.Substitute(cell.Value, char, replaceText)
  End Function
  

  This function allows you to replace any character, not just asterisks.
• Call this function in your worksheet:

   =CustomReplace(A1,“*”,“@”) 

Final Thoughts

Mastering the art of replacing asterisks in Excel significantly enhances your ability to manage, clean, and analyze data efficiently. Whether you’re dealing with spreadsheets for financial reporting, inventory management, or data entry, these methods provide you with the tools to make your Excel experience smoother. Remember, every Excel user’s journey begins with learning these small but powerful techniques that can drastically improve your workflow. Apply these strategies to streamline your data manipulation tasks, and you’ll find yourself navigating through Excel like a pro.

Can I use these methods to replace other special characters?

+

Yes, most of these methods can be adapted to replace other special characters. For wildcards like asterisks or question marks, remember to use escape characters or the SUBSTITUTE function.

Is there a way to undo replacements in Excel?

+

Excel does not automatically log changes. However, you can use “Ctrl + Z” to undo the last action or save your work before performing replacements to have a backup option.

Do these methods work in all versions of Excel?

+

Most of these methods work in all modern versions of Excel, but check your version’s compatibility, especially for advanced features like Power Query or VBA macros.