VBA find "text" and replace adjacent characters

em-digity

New Member
Joined
Nov 8, 2011
Messages
5
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If the search text will never appear in the titles, perhaps this:

Code:
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
 
Last edited:

em-digity

New Member
Joined
Nov 8, 2011
Messages
5
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" :confused:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are Lot:, Layer: and Recipe: consistent values in the string?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
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

New Member
Joined
Nov 8, 2011
Messages
5

ADVERTISEMENT

Code:
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:"
 
Last edited:

em-digity

New Member
Joined
Nov 8, 2011
Messages
5
Did I lose you with that last one? It's more complicated than it first seems.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top