How to find and replace asterisk / question mark / tilde in Excel?

When handling large datasets or maintaining templates in Excel, you may encounter situations where certain special characters such as asterisks (*), question marks (?), or tildes (~) are present in your data. These characters often serve as wildcards or command symbols in Excel search operations and formulas, which can cause issues when you simply want to search for the literal character itself. For example, data exports from other systems or user entries might contain these symbols as part of filenames, product codes, or descriptive text.

Attempting to directly find and replace these special characters by typing them into the Excel Find and Replace dialog does not always yield the expected results. This is because Excel interprets "*" and "?" as wildcard characters — "*" matches any sequence of characters, and "?" matches a single character. The tilde "~" is used as an escape character to indicate that the character immediately following it should be treated literally. If you use these in their default form for searching, you may inadvertently change much more data than intended.

In this article, we'll explain in detail how to quickly and accurately find and replace asterisks, question marks, or tildes in Excel, and introduce several flexible solutions that fit a variety of work scenarios.

Find and replace asterisk / question mark / tilde in Excel

Excel formula: SUBSTITUTE function to replace special characters

VBA macro: Find and replace special characters with code

Find and replace asterisk / question mark / tilde in Excel

To find and replace these particular characters within your worksheet — regardless of whether they appear at the start, end, or within other text — you'll need to prefix them with a tilde character (~) in the Find what box. This tells Excel to search for the actual character rather than interpret it as a wildcard or special command. This approach works well for data cleanup or reformatting tasks across simple tables, but may have limitations if you need to automate or repeatedly process large data ranges.

1. Select the range of cells where you want to find and replace the special characters. If you want to search the entire worksheet, you can click any cell before performing the next steps; otherwise, select the relevant area to limit the replacement scope.

2. Go to the Home tab, click Find & Select > Replace, or use the convenient shortcut Ctrl + H to open the Find and Replace dialog box. If working with large datasets, using the shortcut can save time and reduce repetitive clicking.

3. In the Find and Replace dialog, enter ~* in the Find what box if you want to search for asterisks, ~? for question marks, or ~~ for tildes. In the Replace with box, type the value or character that you want to use as the replacement. This allows you to substitute these special characters with text, numbers, or simply remove them by leaving the box blank, depending on your needs.

4. Click the Replace All button to perform the operation. Excel will return a confirmation dialog showing how many replacements were made. Carefully review the results in your data to ensure the operation has only affected the intended cells.

5. Finally, close the Find and Replace dialog box after confirming your changes.

Note: When replacing multiple types of special characters in the same range, you'll need to repeat the procedure for each character. Be attentive with data containing formulas, as replacing parts of formula text can lead to errors. Always consider making a backup of your worksheet before performing large-scale replacements to avoid data loss or unexpected results.

Tip: If you're searching for a literal tilde, use ~~ in the Find what field. To replace a question mark, type ~?. These allow you to precisely control what gets changed even in complex strings of text or product codes.

Advantages: This built-in feature is simple and effective for quick replacements when you don't need automation or advanced logic.

Limitations: Each replacement operation must be performed manually, and it is less suitable for repeated replacements across many sheets or files.

Troubleshooting: If you notice no matches are found, double-check that you've correctly included the tilde (~) before the special character in the Find what box. To avoid affecting formulas, you may wish to filter your data to only target specific columns or text cells, rather than including an entire worksheet.

Excel formula: SUBSTITUTE function to replace special characters

If you prefer a formula-based approach, Excel's SUBSTITUTE function offers a dynamic way to replace characters directly within worksheet cells. This method is especially helpful if you want to maintain your original data and show the cleaned or updated text in separate columns. It's suitable for situations where changes may need to be undone or reviewed, and is easily adapted for processing lists or tables with hundreds or thousands of items.

1. Suppose you need to replace asterisks "*" in the values of column A. Enter the formula below in the target cell, such as B1:

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

This formula replaces every asterisk in cell A1 with the text “replacement”. You can replace "replacement" with any text, number, or leave it as an empty string ("") to simply remove the asterisks.

2. Press Enter to confirm the formula. To apply the formula to other rows, select cell B1, copy it (Ctrl+C), then select the range you wish to copy the formula to, and paste (Ctrl+V). Excel will automatically adjust references so that each row processes the value in column A.

3. To replace question marks "?" or tildes "~", use similar formulas. For example:

=SUBSTITUTE(A1,"?","replacement")

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

You can combine multiple SUBSTITUTE functions for more complex replacement needs, such as removing both "*" and "?" from text:

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

This nests one SUBSTITUTE inside another to remove both symbols at once.

Advantages: Formula solutions update automatically as your source data changes, allow side-by-side comparison, and are safe for repeated use.

Limitations: The results are separate from your original data; if you need to overwrite original values, you'll need to copy and paste the results as values.

Troubleshooting: The SUBSTITUTE function only works with plain values. If your data contains formulas, array formulas, or requires case-sensitive replacements, additional steps or helper columns may be needed. If characters are not being replaced as expected, confirm they are the actual standard characters and not lookalike symbols from other languages or system encodings.

VBA macro: Find and replace special characters with code

If you frequently need to find and replace asterisks (*), question marks (?), or tildes (~) across multiple sheets or large datasets, automating the process with a custom VBA macro is highly effective. This approach is ideal for batch operations and repetitive tasks, providing flexibility far beyond manual or formula-based methods. Macros can be tailored to replace within all cells, specific columns, across multiple workbooks, or even within only selected sheets.

1. To get started, enable the Developer tab in Excel (if it’s not already visible), then click Developer > Visual Basic to open the VBA editor. In the VBA window, click Insert > Module, and paste the code below into the module:

Sub ReplaceSpecialCharacters()
    Dim ws As Worksheet
    Dim rng As Range
    Dim oldChar As String
    Dim newChar As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to process", xTitleId, rng.Address, Type:=8)
    
    oldChar = Application.InputBox("Enter the character to replace (*, ?, or ~)", xTitleId, "", Type:=2)
    newChar = Application.InputBox("Enter the new character or value", xTitleId, "", Type:=2)
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = Replace(cell.Value, oldChar, newChar)
        End If
    Next cell
End Sub

2. To run the macro, click the (Run) button while the cursor is inside the macro. A prompt will appear for you to select the range to process, then specify which character to replace (enter * or ? or ~ as needed, without quotes), and finally enter the replacement value or character. The macro will process each cell in the selected range accordingly.

Advantages: VBA automation is well-suited for handling large quantities of data, cross-sheet operations, or for embedding in more complex data processing workflows. It saves time for repetitive or large-batch replacement tasks.

Limitations: Code changes original cell values directly, so work on a backup or make a copy before running macros. Users should review the data range and replacement parameters carefully, as there is no built-in undo in VBA after execution. Macros require users to enable macro functionality in Excel and may be restricted in work environments with strict security settings.

Troubleshooting: If you encounter errors running the macro, verify that macros are enabled and there is no worksheet protection on the cells you are targeting. Enter only the character you wish to replace — do not use wildcards or escape sequences in the input box. If the macro is not making replacements, check that your range selection includes the cells you intend to modify.

Related articles:

How to find and replace specific text in text boxes?

How to find and replace text in chart titles in Excel?

How to change multiple hyperlink paths at once in Excel?