find and replace certain pattern within string using vba

D

Drew

Board Regular
Joined
Feb 18, 2002
Messages
187
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello All,
I created a vba procedure for a particular job but within that procedure I need to perform another task. I've been trying to come up with a regular expression but to no avail.
I'm looking to loop through a range of cells for any string where there isn't a space before and after a hyphen. When occurance is matched, then replace with spaces. One exception to the rule would be if data is contained within parentheses, then ignore.
There could be zero occurances in string to many.

ie.
abc - def ==> ok, s/b skipped
abc - def (nnn-mmmm ooo) ==> ok, s/b skipped
abc-def (nnn-mmmm ooo) ==> abc - def (nnn-mmmm ooo)
abc- def (nnn-mmmm ooo) ==> abc - def (nnn-mmmm ooo)
abc -def (nnn-mmmm ooo) ==> abc - def (nnn-mmmm ooo)
abc-def ==> abc - def
abc- def ==> abc - def
abc -def ==> abc - def

Thanks in advanced,
Drew
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
For want of anything better, try this:-
Data in column "A"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG12Apr16
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] txt         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] v           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] fd          [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
fd = False
[COLOR="Navy"]For[/COLOR] v = 1 To Len(Dn)
    txt = Dn.Characters(v, 1).Text
    [COLOR="Navy"]If[/COLOR] txt = "(" [COLOR="Navy"]Then[/COLOR]
        fd = True
    [COLOR="Navy"]ElseIf[/COLOR] txt = ")" [COLOR="Navy"]Then[/COLOR]
        fd = False
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Dim[/COLOR] t
[COLOR="Navy"]If[/COLOR] fd = False [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Dn.Characters(v, 1).Text = "-" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dn.Characters(v - 1, 2).Text = " -" [COLOR="Navy"]Then[/COLOR]
            Dn.Characters(v, 1).Text = Replace(Dn.Characters(v, 1).Text, "-", " -")
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Not Dn.Characters(v, 2).Text = "- " [COLOR="Navy"]Then[/COLOR]
            Dn.Characters(v, 1).Text = Replace(Dn.Characters(v, 1).Text, "-", "- ")
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] v
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
This macro should do what you want (just change the constant assignments in the Const statements to match your actual data setup)...
Code: 
Sub AddMissingSpacesAroundDashes()
  Dim X As Long, Z As Long, LastRow As Long, R As Range
  Dim PartsParen() As String, PartsLeft() As String
  Const DataCol As String = "A"
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  For X = StartRow To LastRow
    PartsParen = Split(Replace(Replace(Cells(X, DataCol).Value, ") ", ")" & Chr(1)), " (", Chr(1) & "("), ")")
    For Z = 0 To UBound(PartsParen)
      If Len(PartsParen(Z)) Then
        PartsLeft = Split(PartsParen(Z), "(")
        PartsLeft(0) = WorksheetFunction.Trim(Replace(PartsLeft(0), "-", " - "))
        PartsParen(Z) = Join(PartsLeft, "(")
      End If
    Next
    Cells(X, DataCol).Value = Replace(Join(PartsParen, ")"), Chr(1), " ")
  Next
End Sub
 
Upvote 0
Rick Rothstein said:
This macro should do what you want (just change the constant assignments in the Const statements to match your actual data setup)...
Code: 
Sub AddMissingSpacesAroundDashes()
  Dim X As Long, Z As Long, LastRow As Long, R As Range
  Dim PartsParen() As String, PartsLeft() As String
  Const DataCol As String = "A"
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  For X = StartRow To LastRow
    PartsParen = Split(Replace(Replace(Cells(X, DataCol).Value, ") ", ")" & Chr(1)), " (", Chr(1) & "("), ")")
    For Z = 0 To UBound(PartsParen)
      If Len(PartsParen(Z)) Then
        PartsLeft = Split(PartsParen(Z), "(")
        PartsLeft(0) = WorksheetFunction.Trim(Replace(PartsLeft(0), "-", " - "))
        PartsParen(Z) = Join(PartsLeft, "(")
      End If
    Next
    Cells(X, DataCol).Value = Replace(Join(PartsParen, ")"), Chr(1), " ")
  Next
End Sub
Click to expand...
Actually, instead of the above code, use this code instead (still adjust the Const statement constants as needed) as it will be about 4 times faster...
Code: 
Sub AddMissingSpacesAroundDashes()
  Dim X As Long, Z As Long, V As Long, LastRow As Long, vArr As Variant
  Dim PartsParen() As String, PartsLeft() As String
  Const DataCol As String = "A"
  Const StartRow As Long = 1
  vArr = Range(Cells(StartRow, DataCol), Cells(Rows.Count, DataCol).End(xlUp))
  For V = LBound(vArr) To UBound(vArr)
    PartsParen = Split(Replace(Replace(vArr(V, 1), ") ", ")" & Chr(1)), " (", Chr(1) & "("), ")")
    For Z = 0 To UBound(PartsParen)
      If Len(PartsParen(Z)) Then
        PartsLeft = Split(PartsParen(Z), "(")
        PartsLeft(0) = WorksheetFunction.Trim(Replace(PartsLeft(0), "-", " - "))
        PartsParen(Z) = Join(PartsLeft, "(")
      End If
    Next
    vArr(V, 1) = Replace(Join(PartsParen, ")"), Chr(1), " ")
  Next
  Range(Cells(StartRow, DataCol), Cells(Rows.Count, DataCol).End(xlUp)) = vArr
End Sub
 
Upvote 0
Mick, thank you for the reply. I stepped through the code but it looks like when the first replace function is applied the string moves ahead 1 character. So if, the second replace function is executed, then Replace(Dn.Characters(V, 1) is off by new char position. I will attempt to adjust.

Rick,
Awesome! Thanks, appreciate the help on this! It's working as designed and fast.
Thanks again,
Drew
 
Upvote 0
Drew said:
I've been trying to come up with a regular expression but to no avail.
Click to expand...

Hi Drew

I'm glad your problem is solved.

Just out of curiosity I tried to write one regex for this problem and this one seems to work:

Code: 
Function HyphenSpace(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(\w)\s*-\s*(\w)(?![^(]*\))"
    .Global = True
    HyphenSpace = .Replace(s, "$1 - $2")
End With
End Function


Ex.:

In B2: =HyphenSpace(A2)


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc-def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc- def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc -def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def (nnn-mmmm ooo) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc-def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc- def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>9</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc -def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">abc - def </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>10</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">(ab-cd) ef- gh i-jk (lm- no) p -q</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">(ab-cd) ef - gh i - jk (lm- no) p - q</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>11</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>
 
Upvote 0
pgc01 said:
Just out of curiosity I tried to write one regex for this problem and this one seems to work:
Code: 
Function HyphenSpace(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(\w)\s*-\s*(\w)(?![^(]*\))"
    .Global = True
    HyphenSpace = .Replace(s, "$1 - $2")
End With
End Function
Click to expand...
Interesting... I did not consider approaching this with a UDF (user defined function) solution. Here is my macro reworked into a UDF...
Code: 
Function DashSpace(S As String) As String
  Dim Z As Long, PartsParen() As String, PartsLeft() As String
  PartsParen = Split(Replace(Replace(S, ") ", ")" & Chr(1)), " (", Chr(1) & "("), ")")
  For Z = 0 To UBound(PartsParen)
    If Len(PartsParen(Z)) Then
      PartsLeft = Split(PartsParen(Z), "(")
      PartsLeft(0) = WorksheetFunction.Trim(Replace(PartsLeft(0), "-", " - "))
      PartsParen(Z) = Join(PartsLeft, "(")
    End If
  Next
  DashSpace = Replace(Join(PartsParen, ")"), Chr(1), " ")
End Function
 
Upvote 0
Rick, thanks again! Another great approach. I appreciate the time spent on this.

PGC, thank you for creating the regex function... driven by your curiosity :) I too, based on my curiosity, was thinking regex could handle this form of string manipulation. Works great too.
I feel greedy for asking one last request... I have one additional pattern that I need to capture. It would be great to have it as one regex (continuation of the above), but if not, I can still run a second pattern after the first routine runs.
Within the data stated above, each record contains a '~' (tilde) separating individual items. What I would like, is to make sure that there are no spaces before and after the tilde.
ie
abc - def ~jklmno~ wx - z~rstuv - hhhh
becomes
abc - def~jklmno~wx - z~rstuv - hhhh

Thanks again,
Drew
 
Upvote 0
Drew said:
Rick, thanks again! Another great approach. I appreciate the time spent on this.

PGC, thank you for creating the regex function... driven by your curiosity :) I too, based on my curiosity, was thinking regex could handle this form of string manipulation. Works great too.
I feel greedy for asking one last request... I have one additional pattern that I need to capture. It would be great to have it as one regex (continuation of the above), but if not, I can still run a second pattern after the first routine runs.
Within the data stated above, each record contains a '~' (tilde) separating individual items. What I would like, is to make sure that there are no spaces before and after the tilde.
ie
abc - def ~jklmno~ wx - z~rstuv - hhhh
becomes
abc - def~jklmno~wx - z~rstuv - hhhh
Click to expand...
I am sure the fix for the RegExp coding is similarly easy, but here is the fix for my above offered code approach (it just needed the two red highlighted additions)...
Rich (BB code): 
Function DashSpace(ByVal S As String) As String
  Dim Z As Long, PartsParen() As String, PartsLeft() As String
  S = Replace(Replace(S, "~ ", "~"), " ~", "~")
  PartsParen = Split(Replace(Replace(S, ") ", ")" & Chr(1)), " (", Chr(1) & "("), ")")
  For Z = 0 To UBound(PartsParen)
    If Len(PartsParen(Z)) Then
      PartsLeft = Split(PartsParen(Z), "(")
      PartsLeft(0) = WorksheetFunction.Trim(Replace(PartsLeft(0), "-", " - "))
      PartsParen(Z) = Join(PartsLeft, "(")
    End If
  Next
  DashSpace = Replace(Join(PartsParen, ")"), Chr(1), " ")
End Function
 
Upvote 0
Thanks Rick! wow, that was fast. Works great!

Thanks again,
Drew
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Locked
  • Question
Find and Replace WB references (VBA)
Replies
7
Views
93
RoryA
RoryA
S
  • Question
Replace/Delete a specific pattern within a string using VBA
Replies
1
Views
1K
shknbk2
S
K
  • Solved
Procedure stopping after first line of code
Replies
6
Views
334
Fluff
Fluff
I
  • Question
VBA to expand a logical Text String – Revisited
Replies
1
Views
299
iantech
I
C
  • Question
Replacing PowerPoint Input using Find and Replace Arrays from Excel through VBA
Replies
0
Views
869
ckcs
C

Forum statistics

Threads
1,203,096
Messages
6,053,512
Members
444,669
Latest member
Renarian

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top