Parse a string to remove each instance of more than 1 space?? 3

[robduck]

Im new to VBA in Outlook, can't say its the nicest language.
Ok, what im trying to do - if possible, is create a tilde delimited file, for each instance of more than 1 space I need to remove it:

BEFORE:
USD United States Dollars 0.5244310047 1.9068285264
EUR Euro 0.6874394261 1.4546736222

AFTER:
USD~United~States~Dollars~0.5244310047~1.9068285264~EUR~Euro~0.6874394261~1.4546736222

Hope this makes sense.
I've looked at replace and trim and others but just isnt going to work, any help would be great.

Regards
A troubled programmer.

 

[fstrahberger]

use the RegExp-object from VBScript Regular Expressions

'------------------------------------------------------------
' This example explains the usage of the RegExp
' object to parse a string and replace values
'
' To use this example, go to Extras --> AddIns
' and inclde 'Microsoft VBScript Regular Expressions 1.0'.
'------------------------------------------------------------


Sub TestIt()

    '--------------------------------------
    ' the source string
    '--------------------------------------
    Dim strParse As String
    strParse = "USD United States Dollars                 0.5244310047          1.9068285264"
    Debug.Print strParse
    
    '-------------------------------------
    ' use RegExp to convert the string
    '-------------------------------------
    Dim oReg As New VBScript_RegExp_10.RegExp
    oReg.Global = True
    oReg.IgnoreCase = True
    oReg.Pattern = " +"         ' + means: search for one or more
    
    '-------------------------------------
    ' convert string to target format
    '-------------------------------------
    strParse = oReg.Replace(strParse, "~")
    Debug.Print strParse
    
End Sub

 

[robduck]

Thats brilliant, didnt realise you could incorporate vbscript into it, your a star.

Thanks

 

[HughLerwill]

<<I've looked at replace and trim and others but just isnt going to work>>

Did you try;

strParse = "USD United States Dollars 0.5244310047 1.9068285264"
Do While InStr(strParse, " ")
strParse = Replace(strParse, " ", " ")
Loop
strParse = Replace(strParse, " ", "~")
strParse = ""

Could be good if you have a lot to do and you are in any kind of a hurry

regards Hugh

 

[strongm]

But RegExp will do lots quickly too ...

 

[HughLerwill]

Well I tried looping both routines 1000 times;

RegExp took 1.43 seconds (object creation and set to nothing included)
My example took 0.03 seconds

RegExp is of course much more flexible and quicker for more complicated tasks, but in this case...

regards Hugh,

 

[RoyVidar]

1.43 seconds with 1000 instantiations of regexp, I don't think that's bad!

To be fair, I think that if I knew I was going to do N iterations, I'd either instantiated the object at form load, or at first run, then not released it until form close, or other relevant event.

In the below tests, the regexp object is instantiated once, but the instantiation is included in the timing. I've used QueryPerformanceCounter for the timing.
[tt]
iterations 10 000 1 000 100 10 2
Instr 205.7478 19.6833 2.0384 0.2456 0.0618
RegExp 68.6204 6.7818 0.9251 0.2755 0.2173[/tt]

If timed after object instantiation, as in instantiating the object in form open, then the result for the last test (run twice) for RegExp was 0.0321...

Roy-Vidar

 

[robduck]

Getting a bit too in-depth for my lack of knowledge.
Hugh, your Instr example works just as well as fstrahberger's, the speed of running both routines is unnoticable, on the plus side I dont need to reference 'VBScript Regular Expressions 1.0' which will be usefull, as I will be putting this code on quite a few pc's.

Thanks all for your help

Rob