Excel macro to do find/replace and string value

Question

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

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.

How is it not working?

Cindy Meister
– Cindy Meister

2018-04-06 21:39:13 +00:00
Commented Apr 6, 2018 at 21:39 — Cindy Meister
– Cindy Meister, Commented Apr 6, 2018 at 21:39
You can do it only using excel formula. No need VBA.

Harun24hr
– Harun24hr

2018-04-07 03:52:32 +00:00
Commented Apr 7, 2018 at 3:52 — Harun24hr
– Harun24hr, Commented Apr 7, 2018 at 3:52

paul bica · Accepted Answer · 2018-04-07 21:10:51Z

1

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

edited Apr 7, 2018 at 21:10

answered Apr 6, 2018 at 22:13

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ron Rosenfeld Over a year ago

I like your approach, but, since the tags may not always have that name, suggest you replace that word with a wild card: ` .Replace "<*>"` and ` .Replace "</*>"`

paul bica Over a year ago

Much better suggestion @RonRosenfeld, since I was thinking of creating a list of tags and looping over them for replacements - thanks

QHarr · Accepted Answer · 2018-04-07 21:13:16Z

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:

Put .UsedRange.Columns("A") = arr If you want in place replacement rather than column B.
Using typed function Chr$ is significantly faster than Chr
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
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.

Collectives™ on Stack Overflow

Excel macro to do find/replace and string value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related