Hi and thanks in advance,
I am using Excel 2003
I have a sheet that has in column A some long strings and I want a quick way, preferably without looping, to replace some characters with another character.
The problem I have run into is that when I use Replace in VBA it is only applied to cells with no more than 1024 characters and so I tried using Substitute but that crashes if there is more than 1271 characters in a cell so I don't know how to process these long strings quickly.
I tried the following and hit the limits mentioned above:
Code:
Range("A:A").Select
Selection.Replace What:=strOld, Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Code:
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.Value = Application.Substitute(.Cells, strOld, strNew)
End With