A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

replace() funciton with multiple tests....



 
 
Thread Tools Display Modes
  #1  
Old November 28th 16, 11:53 PM posted to microsoft.public.excel.programming
Matthew Dyer
external usenet poster
 
Posts: 175
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?
Ads
  #2  
Old November 29th 16, 12:08 AM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,451
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  
Old November 29th 16, 07:46 AM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,451
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  
Old November 29th 16, 03:35 PM posted to microsoft.public.excel.programming
Walter Briscoe
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("(?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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 08:48 PM
Multiple Conditional tests with Sumproduct ryguy7272 Excel Worksheet Functions 6 December 20th 08 04: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 11:31 AM
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 12:18 AM.


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