CODEWITHSUNDEEP
excelvba
Grant SDC
Grant SDC

Reputation: 30

Excel macro to do find/replace and string value

As part of an end process dump of data from an application I manipulate the raw output so myself and my team can use further.

I currently manually alter this data but I have been experimenting with excel macro's to try carry out the same action. A basic example of what I am trying to achieve is:

Sample Data

Sample Data

Create a macro to do a find and replace to remove the tags from the raw data and add to the cell value to have a final set of data like ' + Cell Value + ',

I have little experience with VBA and have put this together from snippets of code found from a few quick searches around this area.

Sub ReplaceMetadataTags()
    Columns("A").Replace    What:="<tag1>", _
                            Replacement:="", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

    Columns("A").Replace    What:="</tag1>", _
                            Replacement:="", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

    Dim myCell As Range
    For Each myCell In Selection
        If myCell.Value <> "" Then
            myCell.Value = Chr(39) & Chr(39) & myCell.Value & Chr(39)
        End If
    Next myCell

End Sub

But lack the experience to debug this further.

If anyone could point me in the right direction I'd appreciate it.

Upvotes: 1

Views: 3050

Answers (2)

QHarr
QHarr

Reputation: 84465

1) Code:

You could try, very fast method using array:

Option Explicit

Public Sub RemoveTags()
    Application.ScreenUpdating = False
    Dim arr()

    With ThisWorkbook.Worksheets("Sheet2")       'Change as required
        arr = Intersect(.Range("A:A"), .UsedRange).Value
        Dim i As Long

        For i = LBound(arr, 1) To UBound(arr, 1)
            If Not IsEmpty(arr(i, 1)) Then
                On Error Resume Next
                arr(i, 1) = Chr$(39) & Chr$(39) & Split(Split(arr(i, 1), ">")(1), "<")(0) & Chr$(39) & ","
                On Error GoTo 0
        Next i

        .UsedRange.Columns("B") = arr
    End With
    Application.ScreenUpdating = True
End Sub

Note:

  1. Put .UsedRange.Columns("A") = arr If you want in place replacement rather than column B.
  2. Using typed function Chr$ is significantly faster than Chr
  3. Using .UsedRange can lead to unexpected results so you may want to be explicit in the range to work with e.g. arr = .Range("A1:A15").Value
  4. Some basic error handling with If Not IsEmpty and On Error Resume Next in case search terms not present.

2) Formula example:

With formula, can't do in cell replacement, but only need one Chr(39), in fact CHAR(39), to display the "'" visibly in the cell. In B1:

 =CHAR(39)&MID(A1,FIND(">",A1,1)+1,LEN(A1)-FIND(">",A1,1)-(LEN(A1)-(FIND("<",A1,FIND(">",A1,1)+1)))-1)&CHAR(39)&","

Note:

You can swop FIND for SEARCH in the above. FIND is case-sensitive. SEARCH is case-insensitive and can include wildcards. Doesn't matter in this context but worth being aware of. You might wrap whole thing in IFERROR to suppress errors if search string not found.

Edit:

Considering error handling it might be simpler to go with .Replace, as per @Paul Bica's answer, credit to him please on that, as less coding.

Upvotes: 1

paul bica
paul bica

Reputation: 10715

You can use something like this

Option Explicit

Public Sub ReplaceTags()

    With Sheet1.UsedRange.Columns("B")

        .Replace "<*>", "''"   'replaces "<Tag>" and "</Tag>"

        .Replace "''", "',"

    End With

End Sub

Sample

Result

Upvotes: 1

Related Questions