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

 

[jolivanes]

Sub Maybe()
    MsgBox Left([A1], InStr([A1], " ")) & Replace(Mid([A1], InStr([A1], " "), 99), "-", ",")
End Sub

 

[jolivanes]

Previous suggestion left a double space. Following leaves a single space.

MsgBox Left([A2], InStr([A2], " ") - 1) & Replace(Mid([A2], InStr([A2], " "), 99), "-", ",")

 

[Rick Rothstein]

Previous suggestion left a double space. Following leaves a single space.

MsgBox Left([A2], InStr([A2], " ") - 1) & Replace(Mid([A2], InStr([A2], " "), 99), "-", ",")

I am pretty sure you know this already, but you can omit the third argument to the Mid function that I highlighted in red above. Unlike Excel's MID function, the third argument to VB's Mid function is optional and, when omitted, returns the remainder of the text automatically.

 

[Peter_SSs]

hope i made sense.

It makes sense, but one sample isn't much to go on. Is there any variety in the structure of your data? If not, could you have instead stated your requirement as
- Replace all "-" characters after the first one, or perhaps
- Replace any "-" characters not within the first two characters in the cell?

It may well be that jolivanes suggestion suits your purpose, though if you could possibly have data like
"(-2.0% 0-1-0 A-Z)" or "This is my text: (-2.0% 0-1-0)"
then dashes other than between digits will be replaced.

If jolivanes suggestion does do the job for you then I believe that it could also be written in a slightly simpler form:

MsgBox Left([A2], InStr([A2], " ") - 1) & Replace([A2], "-", ",", InStr([A2], " "))

In case you do have more variety in your data and you do need a RegExp solution then try with

.Global = True
.Pattern = "(\d)(\-)(?=\d)"

and then use something like this to do the substitutions

.Replace(c.Value, "$1,")

 

[jolivanes]

This would work also.

For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    c.Offset(, 1).Value = Left([c], 3) & Replace(Mid([c], 4), "-", ",")
Next c

@Rick
Sorry about that Rick. I answered to you but I must not have saved it.
Anyway, no I was not aware of that but now I am of course. Thank you for that.

 

[Peter_SSs]

This would work also.

.. if all the data is basically identical to the one sample. And if that was the case of course your splitting of the text could be after any position from 2 to 8 inclusive.

 

[jolivanes]

@ Peter.
Yes, I just assumed that the OP knows what he/she has for data and that is what was shown in the first post.
However, we all know that quite often the 2nd question, or the third or fourth, will start with: "Yes, but ........."
All in good humour.

 

[ilcaa]

It makes sense, but one sample isn't much to go on. Is there any variety in the structure of your data? If not, could you have instead stated your requirement as
- Replace all "-" characters after the first one, or perhaps
- Replace any "-" characters not within the first two characters in the cell?
[/code]

your right, i was a little lazy in my post last night... the data comes from the internet so it is not consistent with spaces, etc except for this pattern of 1-3-2. i want to implement Peters regex suggestion but am having trouble with the syntax of the code. here are more details.

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

when i try this code i get TRUE as cell value

Sub test()

Dim regex As RegExp
Set regex = New RegExp

Dim rng As Range
Set rng = Range("a1:a1000")

With regex
    .Global = True
    .Pattern = "(\d)(\-)(?=\d)"

    With rng
        .Value = .Replace(.Value, "$1,")
    End With
end with
        
End Sub

 

[Rick Rothstein]

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

Are the numbers that are separated by the dashes always single digit numbers as your examples all show?