Replace text in a column using a range of substitute data in Excel

Posted on Thu 21 May 2015 in excel, vba, howto • 2 min read

The other day I had to replace a bunch of text in a column in Excel from a range of text values in another column. The standard substitute formula doesn't seem to work with a range of data so I fugured I'd have a look a doing this with a macro. Turns out that I REALLY suck at vba.

What would have taken me a minute in java or PHP wound up taking an hour to figure out.

The basic scenario is that I have a hundred thousand rows of text. I'd like to replace all instances of the text in Column B, found in the long string of text in column C, with the text in Column A.

Input:

Replacement text Find text Search text
food foo lorem ipsum foo dolor sit bar amet baz blah
bard bar lorem ipsum foo dolor sit bar amet baz blah
bazd baz lorem ipsum foo dolor sit bar amet baz blah

Output:

Replacement text Find text Search text
food foo lorem ipsum food dolor sit bard amet bazd blah
bard bar lorem ipsum food dolor sit bard amet bazd blah
bazd baz lorem ipsum food dolor sit bard amet bazd blah

The following is my amateurish attempt at building a VBA macro:

Sub ReplaceFormulaText()

    Dim currFindCell As String
    Dim currFindString As String
    Dim currReplaceString As String

    Dim findCell As String
    Dim replaceCell As String
    Dim searchText As String
    Dim startRow As String

    findCell = InputBox("Enter find Column:", "Find Cell")
    replaceCell = InputBox("Enter replace Column:", "Replace Cell")
    searchText = InputBox("Enter search Column: ", "Search cell")
    searchText = searchText & ":" & searchText
    startRow = InputBox("Enter start Row:", "Start Row")

    findCell = findCell & startRow

    Range(findCell).Select
    Do Until IsEmpty(ActiveCell)
        currFindCell = ActiveCell.Address
        currFindString = ActiveCell.Value

        currReplaceString = Range(replaceCell & ActiveCell.Row).Value

        Columns(searchText).Select
        Selection.Replace What:=currFindString, Replacement:=currReplaceString, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat _
            :=False, ReplaceFormat:=False

        Range(currFindCell).Select
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub

...It worked, but it ain't pretty