Find and Replace or RegEx Help

[nickyc]

Hi guys,

I have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?

E.g.

Before: [ +Test +Test]
After: [+Test +Test]

    Columns("D:D").Select
    Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Richard Schollar]

Hi

Try this

Sub Replace_Char()
Dim r As Range, cell As Range

Set r = Application.Intersect(ActiveSheet.UsedRange,ActiveSheet.Range("D:D"))

With CreateObject("vbscript.regexp")
  .Global = False
  .Pattern = "( )(.*\+)"
  For Each cell In r
    cell.Value = .Replace(cell.Value,"$2")
  Next cell
End With
r.Replace What:="+", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[nickyc]

Hi there,
I keep getting a type mismatch error when I have:

+Test +Test
=+Test +Test

The second data shows as <TABLE style="WIDTH: 75pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=100><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=100 align=center>#NAME?</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 75pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=100><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=100>+Test +Test</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=center>#NAME? (this is </TD></TR></TBODY></TABLE>

 

[Leith Ross]

Hello nickyc,

Try this macro. It will replace all the "+" signs and any single space prefixes.

Sub FindAndReplace()

  Dim Cell As Range
  Dim RegExp As Object
  Dim Rng As Range
      
      Set Rng = ActiveSheet.UsedRange.Columns(4).Cells
  
      Set RegExp = CreateObject("VBScript.RegExp")
      RegExp.Global = True
      RegExp.Pattern = "\s?\+"
      
          For Each Cell In Rng
            Cell = RegExp.Replace(Cell, "")
          Next Cell
          
End Sub

 

[nickyc]

Hi there,

Still getting a mismatch error. Does it have something to do with the = sign as the String cell?