replace function truncates text

[Marcelo Rizzo]

Hi y'all:

I am sorry if this is a repost I can seem to find the original message.


I am haveing a peculiar problem with the replace function of excel 200 and
2002.
I am replacing a token (@@@@@@) with a vblf within a range of cells.
The macro works fine when in most cases. But when the cells contains large
blocks of text, around 900 characters, anything above that disappears.

Any clue
Thanks
Marcelo Rizzo

 

[JohnI]

Marcelo,

I had a similiar problem with the SUBSTITUTE function after character 911 in
a long text cell.
Which function are you using?

It must be some deficiency in Excel.

I'll be interested to see anyone else posting on this topic with more info.

regards,

JohnI

 

[Marcelo Rizzo]

That sound about right.
I wonder if the 911 is an ms programmer's idea of a joke. Emergency
Help,Help!!

I am using the REPLACE function.
I also tried to using vba. But when I select the text (str$ = range.select)
not all of the text is extracted. And to add insult to injury, when the text
is placed back in the cell (range.formulaR1C1) even more text is cropped.
So, I think you are right is definately a deficiency.


Cheers,
Marcelo

 

[Dave Peterson]

Maybe you could try this:

'for xl2k and above
Option Explicit
Sub testme01()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = replace(myCell.Value, "$$$$", Chr(10))
Next
End Sub

'for xl97
Option Explicit
Sub testme02()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = Application.Substitute(myCell.Value, "$$$$", Chr(10))
Next
End Sub

I put some test data into a cell. About 1200 characters. Each worked ok, and
I'm using xl2002.

 

[Marcelo Rizzo]

Thank You that worked like a charm


Marcelo

Maybe you could try this:

'for xl2k and above
Option Explicit
Sub testme01()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = replace(myCell.Value, "$$$$", Chr(10))
Next
End Sub

'for xl97
Option Explicit
Sub testme02()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = Application.Substitute(myCell.Value, "$$$$", Chr(10))
Next
End Sub

I put some test data into a cell. About 1200 characters. Each worked ok, and
I'm using xl2002.