regexvba

Combine three functions into one function to remove all possible spaces around a specific character and a digit(s)


Note: Before I post my question,
I got a suggestion that this Link to answer my question, I have checked it and it dose not.

Because there are more than one user input values on our database system (later exported into an Excel spreadsheet).
So the manual entry of multiplication pattern by users occurs to be different each time.
example 1: "3 x2"
example 2: "4x 8"
example 3: "6 x 10"
as you will notice there may be a space between "X" character and the digits in a different position each time.
Because my expertise with regular expression is on the beginning stages,
I have been created three functions to deal with each example above and they works respectively correctly as it should.
But, I seek to combine these function into one function (if it is possible) for easier use.
In Advance, great thanks for your help.

Current Values Expected Result
Last the 3 x2 Injection Last the 3x2 Injection
Last the 4x 8 Injection Last the 4x8 Injection
Last the 6 x 10 Injection Last the 6x10 Injection
Function Remove_Space_between_Digit_X(ByVal str As String) As String
   Static reg As New RegExp
     reg.Global = True
      reg.IgnoreCase = True
      
       reg.Pattern = "(\s[0-9]+)(\s)(x)"
       
    Remove_Space_between_Digit_X = re.Replace(str, "$1$3")
End Function

Function Remove_Space_between_X_Digit(ByVal str As String) As String
   Static reg As New RegExp
     reg.Global = True
      reg.IgnoreCase = True
      
       reg.Pattern = "(x)(\s)([0-9]+)(\s)"
       
    Remove_Space_between_X_Digit = reg.Replace(str, "$1$3$4")
End Function

Function Remove_Space_around_X_Digit(ByVal str As String) As String
   Static reg As New RegExp
     reg.Global = True
      reg.IgnoreCase = True
      
       reg.Pattern = "([0-9])+(\s)(x)(\s)([0-9]+)(\s)"
       
    Remove_Space_around_X_Digit = reg.Replace(str, "$1$3$5$6")
End Function

Solution

  • You can use this regex:

    (\d)\s*x\s*(\d)
    

    which will match:

    • (\d) : a digit (captured in group 1)
    • \s*x\s* : an x surrounded by some number of spaces
    • (\d) : a digit (captured in group 2)

    and replace it with $1x$2 to remove any spaces in the string.

    Demo on regex101

    In VBA:

    Function Remove_Space_around_X(ByVal str As String) As String
        Static reg As New RegExp
        reg.Global = True
        reg.IgnoreCase = True 
        reg.Pattern = "(\d)\s*x\s*(\d)"
           
        Remove_Space_around_X = reg.Replace(str, "$1x$2")
    End Function
    

    Note

    In the interest of simplicity the regex is not as strict as it could be; it will also match something like a4 x 3b. If this could be an issue, please instead use:

    \b(\d+)\s*x\s*(\d+)\b
    

    which uses word breaks and matches multiple digits to ensure that it only matches multiplications of numbers.

    Regex demo on regex101