Macro search replace question

  • Thread starter Thread starter WDP
  • Start date Start date
W

WDP

We're trying to eliminate all occurances of 4 numbers followed by a colon
and a space (1234: ) in the begining of the string. We've tried find("????:
", A1) but it finds everything.

Does anyone know a simple way ???

Bill
 
I make the assumptions that the data is in column A and that you have
"####: " at the beginning of the text and may be in the middle of the text
as well. Make a copy of your file before you try this.

Sub rep()
Dim xC As Range, pos1 As Long, pos2 As Long
Dim str1 As String
For Each xC In ActiveSheet.Range(Cells(1, "A"), Cells(65536, "A").End(xlUp))
str1 = xC.Text
If Len(str1) Then
pos1 = InStr(str1, ": ") + 2 'leading "*: "
str1 = Mid$(str1, pos1)
pos2 = InStr(str1, ": ") 'second "*: "
If pos2 Then
pos1 = InStrRev(str1, " ", pos2)
str1 = Left$(str1, pos1) & Mid$(str1, pos2 + 2)
End If
xC = str1
End If
Next
End Sub

As an example to include other columns, say columns A to C, change
Cells(65536,"A") to Cells(65536,"C")
 
Back
Top