This post will guide you how to find or replace a character such as a tilde, an asterisk or a question mark using Find & Replace function in excel. How to find or replace wildcard characters in excel.

Wildcards are special characters that can be used to take any place of any character. And there are three wildcard characters in excel: asterisk (*), question mark (?) and tilde. And the asterisk character can be used to represents any number of characters. The question mark can be used to represents only one single character. And the tilde can be used to identify a wildcard character in the text string.

So Excel use the tilde (~) as a marker to indicate that the next character is a literal. When you want to use the Find and Replace function to find or replace wildcard character, you must add a tilde (~) before the character in the Find What box.

1. Find and Replace Tildes and Wildcard Characters

To find and replace wildcard characters in excel, you can follow these steps:

#1 Select the range of cells that you want to find or replace the wildcard characters.

#2 go to HOME tab, click Find&Select command under Editing group. Or you can press Ctrl +H keys to open the Find and Replace dialog box directly. The Find and Replace dialog box will appear.

#3 switch to Replace tab, type ~* in the Find what text box, then type the replaced characters in the Replace with box. Click Replace All button.

#4 you will see that all the asterisk character are replaced with the specified character in your selected range.

2. Find and Replace Tildes and Wildcard Characters using VBA Code

Now, let’s explore the second method, which involves using VBA code to find and replace tildes and wildcard characters. This method offers a more customizable approach and allows for automation of the process.”

Press ALT + F11 to open the VBA editor window. Alternatively, you can navigate to the “Developer” tab (if not visible, enable it in Excel settings), and click on “Visual Basic” in the “Code” group.

In the VBA editor window, go to the “Insert” menu and select “Module.” This will create a new module in the project.

Copy the provided VBA code.Paste the code into the newly created module in the VBA editor window.

Sub FindReplaceTildesAndWildcards()
    Dim ws As Worksheet
    Dim cell As Range
    Dim findString As Variant
    Dim replaceString As String
    Dim i As Integer
    Dim sourceRange As Range
    Dim selectedRange As Range
    
    ' Prompt the user to select the source range
    On Error Resume Next ' Resume execution if the user cancels the selection
    Set selectedRange = Application.InputBox("Select the source range:", Type:=8)
    On Error GoTo 0 ' Restore error handling
    
    ' Check if a range was selected
    If selectedRange Is Nothing Then
        MsgBox "No range selected. Macro will exit.", vbExclamation
        Exit Sub
    End If
    
    ' Define the strings to find and replace
    findString = Array("~", "*", "?")
    replaceString = ""
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each cell in the selected range
        For Each cell In selectedRange
            ' Loop through each findString
            For i = LBound(findString) To UBound(findString)
                ' Check if the cell contains the findString
                If InStr(1, cell.Value, findString(i)) > 0 Then
                    ' Replace the findString with replaceString
                    cell.Value = Replace(cell.Value, findString(i), replaceString)
                End If
            Next i
        Next cell
    Next ws
End Sub

Close the VBA editor window by clicking the close button (X) or pressing ALT + Q. This will return you to the Excel workbook.

Press ALT + F8 to open the “Macro” dialog box.

Select the macro named “FindReplaceTildesAndWildcards” from the list.

Click “Run” to execute the macro.

After running the macro, a prompt will appear asking you to select the source range.

Click and drag to select the range of cells from which you want to remove tildes, asterisks, and question marks.

Once you’ve selected the range, click “OK” or press “Enter” to confirm your selection.

The macro will then loop through each cell in the selected range and remove the specified characters.

Once the macro finishes running, check the selected range to verify that the tildes, asterisks, and question marks have been removed from the cells.

3. Video: Find and Replace Tildes and Wildcard Characters

This Excel video tutorial, we’ll explore two methods to find and replace tildes and wildcard characters. We’ll start by using the built-in ‘find and replace’ feature, followed by a method utilizing VBA code.