Replace text marker with column text

Question

I am having several columns with text. Furthermore, I have a column, which is called Replacement text. This columns contains strings that have markers in it, like [1], [2], etc.

I would like to replace the markers with the text that is in the marker`s row.

For example, Here you can find [5] becomes Here you can find b, because [5] is the markers column and in the row of the string b is the value for the marker.

I was thinking of creating a large if-else construct and substitute the text, which is extremely error-prone.

However, I kindly ask you if there is an easier solution?

I appreciate your input!

Answer 1

This answer is highly plagiarised from Thomas Inzina's first version of his answer, but a very simple way of performing the replacement would be:

Sub ReplaceText()
    Dim r As Long
    Dim c As Long
    With ActiveSheet
        For r = 3 To .Range("K" & .Rows.Count).End(xlUp).Row
            For c = 1 To 10
                .Cells(r, "K").Replace "[" & c & "]", .Cells(r, c).Value
            Next
        Next
    End With
End Sub

The above code will attempt to do a substitution using all ten columns.

As Thomas has noted, the following code will only do the substitution if a substitution is necessary and therefore could be an order of magnitude faster, so is undoubtedly a better solution:

Sub ReplaceText()
    Dim r As Long
    Dim c As Long
    With ActiveSheet
        For r = 3 To .Range("K" & .Rows.Count).End(xlUp).Row
            For c = 1 To 10
                If Instr(.Cells(r, "K").Value, "[" & c & "]") > 0 Then
                    .Cells(r, "K").Replace "[" & c & "]", .Cells(r, c).Value
                End If
            Next
        Next
    End With
End Sub

(Many thanks to Thomas for his effort in performing speed tests on the two different methods.)

Answer 2

ReplaceBrackets1: uses RegEx to extract the column number. It takes 15.03 Seconds to processed 100K records.

---

Sub ReplaceBrackets1()
    'http://analystcave.com/excel-regex-tutorial/
    Dim c As Range
    Dim Match As Object, Matches As Object, regex As Object
    Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = True
        .Pattern = "\[(.*?)\]"
    End With

    For Each c In Range("K3", Range("K" & Rows.Count).End(xlUp))
        If regex.Test(c.Text) Then
            Set Matches = regex.Execute(c.Text)
            For Each Match In Matches
                c.Replace Match, c.EntireRow.Columns(CInt(Match.SubMatches(0)))
            Next Match

        End If
    Next
End Sub

ReplaceBrackets2: loades the data into arrays, uses RegEx to extract the column number and only writes to the worksheet 1 time. It takes 1.27 seconds to process 100K records.

---

Sub ReplaceBrackets2()
'http://analystcave.com/excel-regex-tutorial/
    Dim x As Long, column As Long
    Dim arData, values
    Dim Match As Object, Matches As Object, regex As Object
    Set regex = CreateObject("VBScript.RegExp")

    With regex
        .Global = True
        .Pattern = "\[(.*?)\]"
    End With

    values = Range("K3", Range("K" & Rows.Count).End(xlUp))
    arData = Range("A3", "L" & UBound(values, 1) + 2)

    For x = 1 To UBound(values, 1)
        If regex.Test(values(x, 1)) Then
            Set Matches = regex.Execute(values(x, 1))
            For Each Match In Matches
                column = Match.SubMatches(0)
                values(x, 1) = arData(x, column)
            Next Match
        End If
    Next

    Range("K3", Range("K" & Rows.Count).End(xlUp)) = values

End Sub

After converting ReplaceBrackets1 into a UDF (getReplacedText) I was amazed to find that it only took 2.53 seconds to fill the formula in for a 100K records. I'm not sure way this would be faster that the original. But having that many formulas really slows down the spreadsheet.

getReplacedText: Uses a Static RegEx to parse the data.

Function getReplacedText(ReplacementText As String, Source As Range)
'http://analystcave.com/excel-regex-tutorial/
    Dim Match As Object, Matches As Object
    Static regex As Object
    If regex Is Nothing Then
        Set regex = CreateObject("VBScript.RegExp")

        With regex
            .Global = True
            .Pattern = "\[(.*?)\]"
        End With
    End If

    If regex.Test(ReplacementText) Then
        Set Matches = regex.Execute(ReplacementText)
        For Each Match In Matches
            ReplacementText = Replace(ReplacementText, Match, Source.Columns(CInt(Match.SubMatches(0))))
        Next Match

    End If

    getReplacedText = ReplacementText
End Function