Removing Asterisks (*) in Excel 2010: A Comprehensive Guide

Learn various methods to effectively remove asterisk characters from your Excel 2010 worksheets, covering simple find/replace, formulas, and VBA.

Asterisks (*) are special characters in Excel, often used as wildcards in functions like VLOOKUP or COUNTIF. However, they can also appear as regular text characters within your data, which might interfere with calculations, sorting, or data analysis. This article provides a comprehensive guide on how to remove asterisks from cells in Excel 2010, offering several methods to suit different scenarios and user preferences.

Method 1: Using Find and Replace (The Quickest Way)

The 'Find and Replace' feature is the simplest and most common method for removing specific characters from your Excel data. When dealing with asterisks, it's crucial to understand Excel's wildcard behavior. An asterisk is a wildcard character, meaning it represents any sequence of characters. To treat it as a literal asterisk, you must precede it with a tilde (~).

1. Select Your Data

Highlight the range of cells where you want to remove the asterisks. If you want to remove them from the entire worksheet, you don't need to select anything.

2. Open Find and Replace

Press Ctrl + H on your keyboard, or navigate to the 'Home' tab, click 'Find & Select' in the 'Editing' group, and then choose 'Replace...'.

3. Enter Find and Replace Values

In the 'Find what:' field, type ~*. The tilde ~ tells Excel to treat the asterisk * as a literal character, not a wildcard. Leave the 'Replace with:' field blank if you want to remove the asterisk entirely, or enter a space if you want to replace it with a space.

4. Execute Replacement

Click 'Replace All' to remove all instances of the literal asterisk in your selected range or worksheet. Alternatively, click 'Replace' to review and replace them one by one.

flowchart TD
    A["Select Range (or entire sheet)"] --> B["Press Ctrl + H"];
    B --> C["Find what: `~*`"];
    C --> D["Replace with: (leave blank or space)"];
    D --> E["Click 'Replace All'"];
    E --> F["Asterisks Removed!"];

Flowchart for removing asterisks using Find and Replace

Method 2: Using Excel Formulas (For Dynamic Removal)

If you need a dynamic solution where the original data remains untouched and the cleaned data appears in a new column, Excel formulas are the way to go. This is particularly useful when you want to preserve the original data for auditing or other purposes. We'll use a combination of SUBSTITUTE and the wildcard escape character.

The SUBSTITUTE function replaces existing text with new text in a string. To make it treat the asterisk as a literal character, we again use the tilde ~ as an escape character.

=SUBSTITUTE(A1,"~*","")

Formula to remove all literal asterisks from cell A1

Let's break down this formula:

1. Identify Target Cell

A1 refers to the cell containing the text from which you want to remove the asterisk.

2. Specify Text to Find

"~*" is the text that the SUBSTITUTE function will look for. The ~ ensures that * is treated as a literal asterisk.

3. Specify Replacement Text

"" (empty string) indicates that the found asterisk should be replaced with nothing, effectively removing it. If you wanted to replace it with a space, you would use " ".

4. Apply and Drag

Enter this formula into an adjacent cell (e.g., B1) and then drag the fill handle down to apply it to the rest of your data.

Method 3: Using VBA (For Advanced Automation)

For more complex scenarios, or if you frequently perform this task across multiple sheets or workbooks, a VBA (Visual Basic for Applications) macro can automate the process. This method requires a basic understanding of the VBA editor.

1. Open VBA Editor

Press Alt + F11 to open the VBA editor. In the 'Project Explorer' pane, right-click on your workbook name, then select 'Insert' -> 'Module'.

2. Insert VBA Code

Paste the following VBA code into the new module. This macro will iterate through the selected cells and replace all literal asterisks.

Sub RemoveAsterisks()
    Dim Rng As Range
    Dim Cell As Range
    
    ' Set the range to work with. If nothing is selected, it will prompt.
    On Error Resume Next
    Set Rng = Application.InputBox("Select a range to remove asterisks from:", "Remove Asterisks", Selection.Address, Type:=8)
    On Error GoTo 0
    
    If Not Rng Is Nothing Then
        For Each Cell In Rng
            ' Replace all literal asterisks with an empty string
            Cell.Value = Replace(Cell.Value, "*", "")
        Next Cell
        MsgBox "Asterisks removed from the selected range!", vbInformation
    Else
        MsgBox "No range selected. Operation cancelled.", vbExclamation
    End If
End Sub

VBA macro to remove asterisks from a selected range

1. Run the Macro

Close the VBA editor. In Excel, go to the 'Developer' tab (if not visible, enable it via File > Options > Customize Ribbon). Click 'Macros', select RemoveAsterisks, and then click 'Run'. The macro will prompt you to select the range.

2. Save Your Workbook

If you want to save the macro with your workbook, you must save it as an 'Excel Macro-Enabled Workbook' (.xlsm format).