Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

replace function truncates text

9 views
Skip to first unread message

Marcelo Rizzo

unread,
Aug 27, 2003, 3:41:45 PM8/27/03
to
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

unread,
Aug 27, 2003, 5:25:19 PM8/27/03
to
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" <mri...@medialocate-usa.com> wrote in message
news:O#fXANNbD...@TK2MSFTNGP12.phx.gbl...

Marcelo Rizzo

unread,
Aug 27, 2003, 5:41:14 PM8/27/03
to
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
"JohnI" <john....@iinet.spamfooler.au> wrote in message
news:%23JCG$GObDH...@tk2msftngp13.phx.gbl...

Dave Peterson

unread,
Aug 27, 2003, 9:39:22 PM8/27/03
to
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.

--

Dave Peterson
ec3...@msn.com

Marcelo Rizzo

unread,
Aug 27, 2003, 10:30:34 PM8/27/03
to
Thank You that worked like a charm


Marcelo


"Dave Peterson" <ec3...@msn.com> wrote in message
news:3F4D5D4A...@msn.com...

0 new messages