deutz wrote:

Auric__;1602931 Wrote:
deutz wrote:

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:-

If the Replace function is in 2003, you can do this:
Selection.Value = Replace(Selection.Value, strOld, strNew)

In 2007 it works for me with a 32,768-character string. (I didn't test
it further than that.)

If the Replace function is *not* in 2003, you can use Split and Join:
Selection.Value = Join(Split(Selection.Value, strOld), strNew)

Also works for me with the same 32,768-character string. I *believe* it
should work as far back as Excel 2000.

Thanks for your suggestions Auric,

I substituted strOld and strNew with my strings and added code line
Range("A:A").Select just before your line of code

I then tried both methods but got a Type Mismatch error each time.

That's because they both expect a single string, i.e. a single cell. Try this
instead:
Dim cell As Range
For Each cell In Selection
cell.Value = Replace(cell.Value, strOld, strNew)
Next

--
Gene pool's running low on chlorine again.