Limits of Substitute and Replace functions

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:

Range("A:A").Select
Selection.Replace What:=strOld, Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Application.Substitute(.Cells, strOld, strNew)
End With

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.

--
- Good lord!
- He's not in.

Originally Posted by Auric__

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.

--
- Good lord!
- He's not in.

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.

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.

Originally Posted by Auric__

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.

Thanks, I have similar code that loops thru each cell and works ok but thought there may be a quicker way to do it without looping, in one fell swoop.

> Thanks, I have similar code that loops thru each cell and works ok but
> thought there may be a quicker way to do it without looping, in one fell
> swoop.

It will always be slower to act directly on the cells. It will be
orders of magnitude faster to 'dump' the range into an array, loop the
array (in memory), then 'dump' the array back into the range.

Example:
Sub ReplaceData()
Dim vData As Variant, i As Long
vData = Selection
For i = LBound(vData) To UBound(vData) 'assumes 1 column selected
vData(i, 1) = Replace(vData(i, 1), strOld, strNew)
Next 'i
Selection = vData
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion