Excel how to replace using wildcard character

microsoft excel

in the excel, I have data like

Series_post
Series_get

I want to find and replace it to

<Series_post>
<Series_get>

I tried

find: Series*
replace: <Series*>

But it doesn't really work. It can find the cells, but replace them with <Series*> all the time.

Best Answer

That type of replace is not a standard functionality in Excel but is do able using VBA:

Sub wrap_in_tag()

    With ActiveSheet.Cells

        Dim c As Variant, firstAddress As String, sFind As String

        sFind = InputBox("Enter search critera, e.g. Series* will result <Series_test>:" _
        , "Enter Critera")

        If sFind = "" Then Exit Sub

        Set c = .Find(sFind, LookAt:=xlWhole)

        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = "<" & c & ">"
                Set c = .Find(sFind, LookAt:=xlWhole)
            Loop While Not c Is Nothing
        End If
    End With
End Sub

Related Solutions

Excel – Does Excel have Find & Replace match placeholder

Excel's pattern matching capabilities are extremely limited: literal characters, * to match any number of unspecified characters, and ? to match a single unspecified character. There is no native match placeholder capability, either in worksheet functions or in VBA.

However, as Excellll noted, you can use regex in VBA, and can develop user-defined functions that are regex-based.

To access regex in your code, add a reference to the "Microsoft VBScript Regular Expression 5.5" library via the Tools->References selections in the main menu for the Visual Basic code editor.

I have not found any comprehensive documentation for the library, but you can find a lot of bits and pieces - and example code - with a Google search.

Excel – How to find and replace the character “*” in excel text values in cell, but NOT formulas

You can use the following routine if you have Excel 2013 or later. Place the code in the ThisWorkbook module. Run once; it will update appropriate cells across the entire workbook.

Sub SetStarsBlank()

    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.UsedRange
            If Not Application.WorksheetFunction.IsFormula(c) Then
                c.Value = Replace(c.Value, "*", "")
            End If
        Next
    Next

End Sub

Best Answer

Related Solutions

Excel – Does Excel have Find & Replace match placeholder

Excel – How to find and replace the character “*” in excel text values in cell, but NOT formulas

Related Question