CODEWITHSUNDEEP
vbaexcel
dataMan
dataMan

Reputation: 13

Excel FInd and Replace Regex Macro

I am trying to create a Macro that is giving me some issue as I have no experience and no idea where to start.

All I am trying to do is create a find and replace Macro that will replace part of a string with nothing.

For example I have the following Custom Field(Id) and all I want the marco to do is remove everything apart fromId.

How can I achieve this?

Code

Sub FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("Custom field(", ")")
rplcList = Array("", "")

'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Next sht
Next x

End Sub

Upvotes: 1

Views: 622

Answers (2)

brettdj
brettdj

Reputation: 55682

Regexp / Variant array solution

Also handles situations where user selects multiple areas in the range.

Sub Retain()
Dim X
Dim rng1 As Range
Dim rng2 As Range
Dim objRegex As Object
Dim lngRow As Long
Dim lngCOl As Long

On Error Resume Next
Set rng1 = Application.InputBox("select range", , Selection.Address, , , , , 8)
On Error GoTo 0

If rng1 Is Nothing Then Exit Sub

Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "[^0-9]"
    .Global = True
For Each rng2 In rng1.Areas
If rng2.Cells.Count > 1 Then
    X = rng2.Value2
        For lngRow = 1 To UBound(X, 1)
            For lngCOl = 1 To UBound(X, 2)
                X(lngRow, lngCOl) = .Replace(X(lngRow, lngCOl), vbNullString)
            Next
        Next
    rng2.Value2 = X
Else
   rng2.Value2 = .Replace(rng2, vbNullString)
End If
Next
End With

End Sub

Upvotes: 0

dot.Py
dot.Py

Reputation: 5157

You can achieve this using Find & Replace without regex!

enter image description here

Solution1:

  1. Select your data
  2. Open Find & Replace module
  3. Replace "Custom Field(" for ""
  4. Replace ")" for ""

Solution2:

You can either use the formula present in this site or use the VBA code posted at this site to remove all the non-numeric characters out of a string.

Sub RemoveNotNum()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    xOut = ""
    For i = 1 To Len(Rng.Value)
        xTemp = Mid(Rng.Value, i, 1)
        If xTemp Like "[0-9]" Then
            xStr = xTemp
        Else
            xStr = ""
        End If
        xOut = xOut & xStr
    Next i
    Rng.Value = xOut
Next
End Sub

Upvotes: 1

Related Questions