Regex to replace dashs between digits

[ilcaa]

i am in the need for a regex for my vba project. i need to replace the - dash that is in-between numbers only, example 1-3-1. The problem is i have negative numbers with dashes which i need to keep, but the patterm is consistent, 1-3-1, should be 1,3,1 so replace dash between digits with a comma

(-2.0% 0-1-0) would convert to (-2% 0,1,0)

hope i made sense. thanks for any help

 

[ilcaa]

thanks for the question. it can be 1 2 or 3 digits in any of the place so, 1-200-102, 14-45-0, etc

 

[Peter_SSs]

Again one example (& no expected result) is not much to go on.
For example, if we had a few examples of the data and the expected results we might know if it was just an oversight that you bolded the two sections below but not the blue one I have underlined which also seems to fit the description.

I have a range of 1000 rows, i want to apply a With Rng (if possible) , here is a sample row, the spaces and commas will vary, but the pattern 2-1-0 will be consistent

1 1-0-2 4.00,100.0%,-2.0 0-1-0 ,-15.00,0.0%,45.0,1-2-4 6.00,100.0%,-2.0,1

With RegExp you cannot process the whole range at once using With Rng.
Assuming that the blue section in the example is also meant to be processed, I think this should work for you.
If not please provide several samples (showing some variety that exists - eg post 11) and the expected results.
This code puts the results in column B for checking. Once satisfied with the code you can alter that B1 near the end to A1 if you want to over-write the original data

Sub ReplaceDashesBetweenDigits()
  Dim a As Variant
  Dim RX As Object
  Dim i As Long
  
  Set RX = CreateObject("VBScript.Regexp")
  RX.Global = True
  RX.Pattern = "(\d+)(\-)(\d+)(\-)(\d+)"
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "$1,$3,$5")
    Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

 

[Peter_SSs]

With RegExp you cannot process the whole range at once using With Rng.

Actually, for 1000 rows I withdraw that statement, though I think I would still use the row-by-row method & it is just as fast by my testing.
Still, if you want to do it in one go ...

Sub ReplaceDashesBetweenDigits_v2()
  Dim RX As Object

  Set RX = CreateObject("VBScript.Regexp")
  RX.Global = True
  RX.Pattern = "(\d+)(\-)(\d+)(\-)(\d+)"
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Application.Transpose(Split(RX.Replace(Join(Application.Transpose(.Value), "|"), "$1,$3,$5"), "|"))
  End With
End Sub

Remove the blue if you want to over-write the original data.

 

[Rick Rothstein]

For those who might be interested, here is a non-RegExp macro that works the same (provided there are no cases of just two numbers with a dash between them or cases of four or more numbers with dashes between them)...

Sub ReplaceDashesBetweenDigits()
  Dim R As Long, X As Long, Arr As Variant
  Arr = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Arr)
    For X = 1 To Len(Arr(R, 1))
      If Mid(Arr(R, 1), X, 3) Like "#-#" Then Mid(Arr(R, 1), X + 1) = ","
    Next
  Next
  Range("B1").Resize(UBound(Arr)) = Arr
End Sub