RegExp Pattern to remove special character

**BS Singh** · 05-14-2014, 03:01 PM

Hi,

I have data in range A1 to A100 with format like AB-CD12-34567890-EF123 and I would like to remove all occurrence of dash (-) except first dash. So the output should be AB-CD1234567890EF123 in Cell B1 and so on.

I know this can be done using other technique however I am trying to learn Regular Expression so would prefer solution trough same.

Please help.

Regards,

**Ron Coderre** · 05-14-2014, 03:23 PM

Here's one approach:

Function CleanChars(mytext As String) As String
Dim RE As Object
Dim REPattern As String
Dim REMatches As Object
Dim Bullpen As String
Dim DashPos As Integer

DashPos = InStr(1, mytext, "-")
Bullpen = Mid(String:=mytext, Start:=DashPos + 1)

Set RE = CreateObject("vbscript.regexp")

REPattern = "-"
RE.Global = True
RE.ignorecase = True
RE.Pattern = REPattern

CleanChars = Left(mytext, DashPos) & RE.Replace(Bullpen, "")
    
End Function

Is that something you can work with?

**BS Singh** · 05-15-2014, 12:12 AM

Thanks Ron, that worked.

Just for my better understanding, Can this be done using only Replace method (without splitting the string) of Regular Exp and if yes then how to tell replace method to replace a particular position of special character. Let say if i have same string with some more digits and dashes and I would like to remove 1,2,5,6 occurrence of special char(-).

Regards,