Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 29th 16, 12:53 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 177
Default replace() funciton with multiple tests....

so basically I'm looking to do the following, which obv doesn't work...

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")

but there's Got to be a more elegant way than just trying to shove a square peg in a round hole with a big hammer... ideas?

  #2   Report Post  
Old November 29th 16, 01:08 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,534
Default replace() funciton with multiple tests....

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


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old November 29th 16, 08:46 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,534
Default replace() funciton with multiple tests....

Hi,

Am Tue, 29 Nov 2016 01:08:10 +0100 schrieb Claus Busch:

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


or another suggestion with Regexp:

Set re = CreateObject("vbscript.Regexp")
ptrn = "purge_|dealdrop_"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True

tempstr = re.Replace(string, "cor010_wcout")


Regards
Claus B.
--
Windows10
Office 2016
  #4   Report Post  
Old November 29th 16, 04:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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 <[email protected]
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("(?urge\|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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If AND/OR funciton with multiple criteria Helen Shah Excel Worksheet Functions 2 November 2nd 09 09:48 PM
Multiple Conditional tests with Sumproduct ryguy7272 Excel Worksheet Functions 6 December 20th 08 05:09 PM
Countif Using Multiple Logic Tests Carl Excel Worksheet Functions 3 June 4th 06 07:09 AM
Multiple tests for a condition Matilda Excel Programming 4 January 6th 06 12:31 PM
Average a group of tests for grade, some tests not taken by all. Scafidel Excel Discussion (Misc queries) 4 August 19th 05 03:50 AM


All times are GMT +1. The time now is 10:36 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017