Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Search and Replace | Excel Discussion (Misc queries) | |||
Search and Replace Question | New Users to Excel | |||
Simple Search and Replace Question | Excel Discussion (Misc queries) | |||
macro to search and replace with offset | Excel Discussion (Misc queries) | |||
search & replace | Excel Worksheet Functions |