Macro search replace question
 

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

Macro search replace question
 

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")


I'm at standstill with about 2,000,000 records to clean. The problem is
that it's in the beginning the string:

34554: roigeoig t4gi4goi4oi t4giht4ih5t t4gt4g 54g 4g6ht4 2655467:
igroi54g
54gg

We can't remove the second occurance of the search string