View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Walter Briscoe Walter Briscoe is offline
external usenet poster
 
Posts: 279
Default replace() funciton with multiple tests....

In message of Tue, 29 Nov 2016 01:08:10 in
microsoft.public.excel.programming, Claus Busch <claus_busch@t-
online.de writes
Hi Matthew,

Am Mon, 28 Nov 2016 15:53:19 -0800 (PST) schrieb Matthew Dyer:

Replace(string, Or("purge_", "dealdrop_"), "cor010_wcout")

I've tried Replace(string, Array("purge_", "dealdrop_"),
"cor010_wcout") which also doesn't work.

since i'll never be in a situation where string will contain both
purge_ and dealdrop_ I've gone the route of running the replace
function twice as follows:

tempstring = Replace(string, "purge_", "cor010_wcout")
tempstring = Replace(string, "dealdrop_", "cor010_wcout")


you can nest to functions:
tempstring = Replace(Replace(string, "purge_", "cor010_wcout"),
"dealdrop_", "cor010_wcout")

or you can do it with an array:

varRep = Array("purge_", "dealdrop")
For i = LBound(varRep) To UBound(varRep)
tempstring = Replace(string, varRep(i), "cor010_wcout")
Next


As usual, Claus pushes my knowledge.
I am NOT suggesting this for Matthew, but I can strongly recommend
Regular Expressions for more complicated replacements than this.
I might (I wouldn't) write
string = getsub("(?:purge\|dealdrop)_", "cor010_wcout").
I have a module to access RE. In that I have:

Option Explicit

Public RE As RegExp ' Needs Tools/References/Microsoft Vbscript
Regular Expressions 1.0 or 5.5

Private Sub EnsureREInitialized()
If RE Is Nothing Then
Set RE = CreateObject("VBScript.Regexp")
RE.Global = True
End If
End Sub
....
Public Function GetSub(ByVal From As String, ByVal p As String, ByVal
Part As String) As String
EnsureREInitialized
RE.Pattern = p
GetSub = RE.Replace(From, Part)
End Function
--
Walter Briscoe