Wilder1626
asked on
VBA - Remove all dashes in cells when dash does not have a number before and after it.
I have this code where i replace all dashes with an empty space.
Now, I would like to replace dashes when the character before AND after are not numbers.
(Format NUMBER & DASH & NUMBER)
Ex:
If I have in the cell:
I see 2 dashes in the same cell. But one of them as a number before and after the dash. So the macro would only replace the St-Mary to St Mary.
If for example i have in the cell:
In this cell, Saint-John dash would be removed as i see a letter before and after the dash, and parc-0234 dash also would be removed as i don't have a combination of a Number before and after the dash.
So it would return to: 10234 Saint John auto parc 0234.
If i have in the cell:
Since before the dash, there is an empty space, it would remove the dash as only format Number & dash & number needs to stay the same. So the result would be: 1234 123456
I need to specify that the text in each cells of column C may have different characters length.
How can i do that.
Thanks again.
Columns("C").Replace " - ", " ", xlPart, xlByRows, True
Now, I would like to replace dashes when the character before AND after are not numbers.
(Format NUMBER & DASH & NUMBER)
Ex:
If I have in the cell:
St-Mary 1234-56
I see 2 dashes in the same cell. But one of them as a number before and after the dash. So the macro would only replace the St-Mary to St Mary.
If for example i have in the cell:
10234 Saint-John auto parc-0234
In this cell, Saint-John dash would be removed as i see a letter before and after the dash, and parc-0234 dash also would be removed as i don't have a combination of a Number before and after the dash.
So it would return to: 10234 Saint John auto parc 0234.
If i have in the cell:
1234 -123456
Since before the dash, there is an empty space, it would remove the dash as only format Number & dash & number needs to stay the same. So the result would be: 1234 123456
I need to specify that the text in each cells of column C may have different characters length.
How can i do that.
Thanks again.
I would not define the dash as an own capture group, as we don't want to keep or work with it. Further the regex shown will work only if there is no digit on either side. I.e.:
matchPattern = "(\D+)-(.*)|(.*)-(\D+)"
replacePattern = "$1 $3"
ASKER
It looks like it work as a formula, but i will just need to transfer that into a macro.
I need to be able to replace actual cell, and not use another cell to give me the results needed.
I need to be able to replace actual cell, and not use another cell to give me the results needed.
@Wilder1626 the function provided will happily replace the cell contents I just implemented it this way to give a demo.
eg
eg
Range("A1").Text = RegexReplace(Range("A1").Text)
ASKER
That was one of the thing i jtested using your file, but i was getting an runtime error 424: Object required.
Range("A1").Text = RegexReplace(Range("A1").Text)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help
I was able to tweak it to:
Dim i As Integer
Dim LastRow As Integer
LastRow = [A65536].End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 1) <> "" Then
Cells(i, 1).Value = RegexReplace(Cells(i, 1).Text)
End If
Next i
I was able to tweak it to:
Dim i As Integer
Dim LastRow As Integer
LastRow = [A65536].End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 1) <> "" Then
Cells(i, 1).Value = RegexReplace(Cells(i, 1).Text)
End If
Next i
Cool. Just a hint
The following will find the last row and work on all versions of excel. Just replace the "A" for the column you want to use.
The following will find the last row and work on all versions of excel. Just replace the "A" for the column you want to use.
lastrow = Range("A" & rows.count).end(xlup).row
Using which regex?
Open in new window
Replace-Example.xlsm