VBA pattern

[mehidy1437]

Hello guys,

.Pattern = "^[0-9.]+,[0-9.]+\,[0-9]{2,3}$"

I want to allow only single . (dot) with the number in this pattern.
So, the input won't be more than one . (dot) with the number,
like 55.50,35.30,100
not like 55..50,35..30,100
How can I do so?

 

[montecarlo2012]

Yes, you can modify the regular expression pattern to allow only one dot (.) in the numbers. Here's an example pattern that should work:

^[0-9]+(\.[0-9]+)?,[0-9]+(\.[0-9]+)?,[0-9]{2,3}$

This pattern matches a string that contains three numbers separated by commas, with the first and second numbers optionally including a decimal point followed by one or more digits.

Here's a breakdown of the pattern:

• ^ matches the start of the string
• [0-9]+ matches one or more digits
• (\.[0-9]+)? matches an optional decimal point followed by one or more digits
• , matches a comma
• [0-9]+(\.[0-9]+)? matches the second number, which can also have an optional decimal point and one or more digits
• , matches a comma
• [0-9]{2,3} matches the third number, which must have 2 or 3 digits
• $ matches the end of the string

To use this pattern in VBA, you can use the Like operator to check whether a string matches the pattern. Here's an example:

Dim input As String
input = "55.50,35.30,100"

If input Like "^[0-9]+(\.[0-9]+)?,[0-9]+(\.[0-9]+)?,[0-9]{2,3}$" Then
    ' input matches the pattern
Else
    ' input does not match the pattern
End If

You can replace the input variable with any string that you want to check against the pattern. If the string matches the pattern, the If statement will evaluate to True, and you can perform some action in the If block. If the string does not match the pattern, the If statement will evaluate to False, and you can perform a different action in the Else block.

 

[JEC]

Here another variant

Function jec(cell As String) As String
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "((\d+(\.|,)?)+?){4}\d{2,3}"
   If .test(cell) Then jec = .Execute(cell)(0)
 End With
End Function

 

[mehidy1437]

Yes, you can modify the regular expression pattern to allow only one dot (.) in the numbers. Here's an example pattern that should work:

^[0-9]+(\.[0-9]+)?,[0-9]+(\.[0-9]+)?,[0-9]{2,3}$

This pattern matches a string that contains three numbers separated by commas, with the first and second numbers optionally including a decimal point followed by one or more digits.

Here's a breakdown of the pattern:

• ^ matches the start of the string
• [0-9]+ matches one or more digits
• (\.[0-9]+)? matches an optional decimal point followed by one or more digits
• , matches a comma
• [0-9]+(\.[0-9]+)? matches the second number, which can also have an optional decimal point and one or more digits
• , matches a comma
• [0-9]{2,3} matches the third number, which must have 2 or 3 digits
• $ matches the end of the string

To use this pattern in VBA, you can use the Like operator to check whether a string matches the pattern. Here's an example:

Dim input As String
input = "55.50,35.30,100"

If input Like "^[0-9]+(\.[0-9]+)?,[0-9]+(\.[0-9]+)?,[0-9]{2,3}$" Then
    ' input matches the pattern
Else
    ' input does not match the pattern
End If

You can replace the input variable with any string that you want to check against the pattern. If the string matches the pattern, the If statement will evaluate to True, and you can perform some action in the If block. If the string does not match the pattern, the If statement will evaluate to False, and you can perform a different action in the Else block.

thanks for the solution.

 

[mehidy1437]

Here another variant

Function jec(cell As String) As String
 With CreateObject("vbscript.regexp")
   .Global = True
   .Pattern = "((\d+(\.|,)?)+?){4}\d{2,3}"
   If .test(cell) Then jec = .Execute(cell)(0)
 End With
End Function

thanks for the option, i will try this in future.