VBScript Regex Replace when numbers are enclosed by quotation marks

Viewed 761

Using Regex in VBScript I need to replace commas that are in decimals enclosed by quotation marks chr(34) with dots

Input
20170927,Ford,,"6025,00",,"0,00",1,T
20170928,"Fiat, Opel",,"13587,17","13587,17",2,N

Output
20170927,Ford,,"6025.00",,"0.00",1,T
20170928,"Fiat, Opel",,"13587.17","13587.17",2,N

I've tried the below code but didn't work.

Dim oRE,text
Set oRE = New RegExp
oRE.Global = True
oRE.Pattern = chr(34)+[0-9],[0-9]+chr(34)
oRE.Replace(text, Replace(text,",","."))

Many thanks.

2 Answers

Part of the problem

In your regex

oRE.Pattern = chr(34)+[0-9],[0-9]+chr(34)
                     ^

enter image description here

The + symbol after the chr(34) instructs the Regex engine to require one or more quotes

I think the + symbol should be moved to after the character set.

oRE.Pattern = chr(34)[0-9]+,[0-9]+chr(34)
                          ^

Proposed solution

You should capture the open/close quote internal digits into capture groups which are then used in the replacement.

Regex ("[0-9]+),([0-9]+")

Replace with: $1.$2

See this Live Demo

Sample input text:

20170927,Ford,,"6025,00",,"0,00",1,T
20170928,"Fiat, Opel",,"13587,17","13587,17",2,N

Sample after replacement

20170927,Ford,,"6025.00",,"0.00",1,T
20170928,"Fiat, Opel",,"13587.17","13587.17",2,N

Sample VB code

Note that the internal double quotes are doubled so that the string is constructed correctly.

VB Script

strTest = "20170927,Ford,,""6025.00"",,""0.00"",1,T" & vbCrLf & "20170928,""Fiat, Opel"",,""13587,17"",""13587,17"",2,N"    
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "(""\d*),(\d+"")"

strTest = re.Replace(strTest, "$1.$2")

VB.net

Dim sourcestring as String = "replace with your source string"
Dim replacementstring as String = "$1.$2"
Dim matchpattern as String = "(""[0-9]+),([0-9]+"")"
Console.Writeline(regex.Replace(sourcestring,matchpattern,replacementstring))

Regex Explanation

NODE                     EXPLANATION
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    "                        '"'
--------------------------------------------------------------------------------
    [0-9]+                   any character of: '0' to '9' (1 or more
                             times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
  )                        end of \1
--------------------------------------------------------------------------------
  ,                        ','
--------------------------------------------------------------------------------
  (                        group and capture to \2:
--------------------------------------------------------------------------------
    [0-9]+                   any character of: '0' to '9' (1 or more
                             times (matching the most amount
                             possible))
--------------------------------------------------------------------------------
    "                        '"'
--------------------------------------------------------------------------------
  )                        end of \2

Try this Code below:

Code:

strTest = "20170928,""Fiat, Opel"",,""13587,17"",""13587,17"",2,N"    'Store the text to be tested in this variable
Set re = New RegExp
re.Global=True
re.Pattern = "(""\d*),(\d+"")"
Set objMatches = re.Execute(strTest)

For Each match In objMatches
    strTest = Replace(strTest,match.Value,match.Submatches.Item(0)&"."&match.Submatches.Item(1))
Next

MsgBox strTest

Output:

enter image description here

Regex used:

("\d*),(\d+")

Click here for Regex Demo

Regex Explanation:

  • ("\d*) - matches a " followed by 0+ occurrences of a digit. Parenthesis captures this whole part in group 1.
  • , - matches , literally
  • (\d+") - matches 1+ occurrences of a digit followed by ". Parenthesis captures this whole part in group 2.
Related