ExcelBanter - Limits of Substitute and Replace functions

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Limits of Substitute and Replace functions (https://www.excelbanter.com/excel-programming/446382-limits-substitute-replace-functions.html)

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:

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

Limits of Substitute and Replace functions

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.

Quote:

Originally Posted by Auric__ (Post 1602931)

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.

Limits of Substitute and Replace functions

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.

Quote:

Originally Posted by Auric__ (Post 1602962)

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.

Limits of Substitute and Replace functions

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