Vba excel replacing string based on table values

Question

I have code which replaces part of string in column A in sheet "Data" with new value written in Column B of sheet "Filter".

Column A of sheet "Filter" has old values, and Column B has values after replacement, see picture:

I know how to replace for example "Griffith RV8" into "Griffith RV-8" but how can I do that replacement plus remove all characters in front of that replaced string?

Griffith RV8 = RV-8

Sub Substitutions()

Dim rngData     As Range
Dim rngLookup   As Range
Dim Lookup      As Range

With Sheets("Data")
    Set rngData = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

With Sheets("Filter")
    Set rngLookup = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

For Each Lookup In rngLookup
    If Lookup.Value <> "" Then
        rngData.Replace What:=Lookup.Value, _
                        Replacement:=Lookup.Offset(0, 1).Value, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        MatchCase:=False
    End If
Next Lookup

End Sub

Why aren't you replacing "Griffith RV8" with "RV-8" ? This doesn't seem like a simple replace - there is some other rule you need to apply? — Tim Williams
– Tim Williams, Commented Jul 20, 2016 at 20:45
Because RV8 will show up with dozens of different words in front of it and all I need from that string is RV-8 and whatever is after — FotoDJ
– FotoDJ, Commented Jul 20, 2016 at 21:14

user6432984 · Accepted Answer · 2016-07-20 20:42:54Z

1

You can use wildcards to replace parts of a string.

Sub Example()

    Range("A1") = "Where in the World is Carmen Sandiego?"

    Range("A1").Replace "*Carmen", "Who is Micheal"

    Range("A3") = "Hello World! How are You?"

    Range("A3").Replace "!*", "? Can you hear me!?"

    Range("A5") = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    Range("A5").Replace "??H??", "fgHij"

    ' You can escape the wildcards using "\"
    Range("A7") = "? not escaped"

    Range("A8") = "WHO? WHO!"

    Range("A8").Replace "WHO?", "WHO ARE YOU?"

    Range("A10") = "? was escaped using a backslash \?"

    Range("A11") = "WHO? WHO!"

    Range("A11").Replace "WHO\?", "WHO ARE YOU?"

End Sub

answered Jul 20, 2016 at 20:42

user6432984

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

Comments

Tim Williams · Accepted Answer · 2016-07-20 21:18:35Z

1

rngData.Replace What:= "* " & Lookup.Value, _
                Replacement:=Lookup.Offset(0, 1).Value, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    MatchCase:=False

will replace [some text here][space]Lookup.Value with Lookup.Offset(0, 1).Value

answered Jul 20, 2016 at 21:18

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Collectives™ on Stack Overflow

Vba excel replacing string based on table values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related