VBA find "text" and replace adjacent characters

[em-digity]

Hey gurus,

Complete newb here. I am trying to build a macro for removing/replacing text from a .log file using VBA. I know how to set up a find/replace macro, but I need to look for something like "Lot: aaaa, Recipe: bbbb, Layer: cccc" where the aaaa, bbbb, cccc are unknown names of unknown lengths.

In this case I would like to replace "aaaa" "bbbb" and "cccc" with "IP Scrubbed" to result in: "Lot: IP Scrubbed, Recipe: IP Scrubbed, Layer: IP Scrubbed"

I don't know how to find for example "Lot:" and replace the adjacent text "aaaa" (which may be up to 25 characters) with "IP Scrubbed"

Is this possible with VBA or am I taking to wrong approach all together?

Thanks in advance.

 

[Scott Huish]

If the search text will never appear in the titles, perhaps this:

Sub test()
Dim t, strTest, u As Long, v
strTest = "Lot: aaaa, Recipe: bbbb, Layer: cccc"
v = Array("aaaa", "bbbb", "cccc")
For u = LBound(v) To UBound(v)
    strTest = Replace(strTest, v(u), "IP SCRUBBED")
Next
MsgBox strTest
End Sub

 

[em-digity]

Thanks guys. Problem is that the "aaaa" "bbbb" or "cccc" are RANDOM values. They will be different every time. So I need a way to search for a known, i.e. "Lot:" or "Layer:" or "Recipe:" and then replace the following random characters up to and not including the next comma with "IP Scrubbed"

 

[Scott Huish]

Are Lot:, Layer: and Recipe: consistent values in the string?

 

[em-digity]

Are Lot:, Layer: and Recipe: consistent values in the string?

Yes

 

[Scott Huish]

Sub test()
Dim t, strTest, u As Long, v
strTest = "Lot: aaaa, Recipe: bbbb, Layer: cccc"
t = Array("Lot: ", "Layer: ", "Recipe: ")
v = Split(strTest, ",")
For u = LBound(v) To UBound(v)
    v(u) = t(u) & "IP SCRUBBED"
Next
strTest = Join(v, ",")
MsgBox strTest
End Sub

 

[em-digity]

Sub test()
Dim t, strTest, u As Long, v
strTest = "Lot: aaaa, Recipe: bbbb, Layer: cccc"
t = Array("Lot: ", "Layer: ", "Recipe: ")
v = Split(strTest, ",")
For u = LBound(v) To UBound(v)
    v(u) = t(u) & "IP SCRUBBED"
Next
strTest = Join(v, ",")
MsgBox strTest
End Sub

Hmmmm, maybe I'm doing something wrong. That didn't work for me. I do see that what I was calling "Recipe" is actually "Recipe Name" but that's not the issue. Here's part of the log file I'd like to scrub:

"Nov 08,00:37:20.593 INFO [Recipes@serverlocalhost] Recipe Loaded Header. Database:Local, Lot:4573Ahr23, Product:Y54A, Layer:3TRO, Recipe Name:X54A_TEV_1A, Class:, Version:"

 

[em-digity]

Did I lose you with that last one? It's more complicated than it first seems.

 

[Scott Huish]

Sub test()
Dim strTest As String, v As Long, w As Long, o, z As Long
strTest = "Nov 08,00:37:20.593 INFO [Recipes@serverlocalhost] Recipe Loaded Header. Database:Local, Lot:4573Ahr23, Product:Y54A, Layer:3TRO, Recipe Name:X54A_TEV_1A, Class:, Version:"
o = Array("Lot:", "Layer:", "Recipe Name:")
For z = LBound(o) To UBound(o)
    v = InStr(strTest, o(z))
    If v Then
        w = Len(Split(Mid(strTest, v), ",")(0))
        strTest = Replace(strTest, Mid(strTest, v, w), o(z) & " IP Scrubbbed")
    End If
Next
MsgBox strTest
End Sub