How do I replace a text string with a line break?

Posted by Tom on April 11, 2001 6:15 PM

Hello,

Here is my dilema in Excel 97: I am looking for a way to replace a string of text with a line break within a cell (i.e. the equivalent to Alt+Enter).

Is there such a way to do this? Perhaps there is an Ascii equivalent that I can use in the replace field?

I stumbled upon a macro that worked for most cells, but once it encountered a cell with too many characters it gives a "Run time error '1004' Formula is too long" message.

Any guidance would be greatly appreciated!!

Posted by Emile P. on April 12, 2001 8:20 AM

Not sure what you are trying to do, but the VBA code for Alt+Enter is Chr(10) - which I got from the macro recorder!

Posted by Tom on April 12, 2001 8:45 AM

Thanks Emile. Actually the code I found includes that. Specifically it is:
Sub ReplaceTags()
Selection.Replace What:="
", Replacement:=Chr(10), LookAt:=xlPart, _
SearchOrder:=xlByRows
End Sub

It works fine on cells with less text, but on the lengthier ones I get the run time error. Any thoughts on how to get around the "Formula is too long" error?

Posted by Emile P. on April 12, 2001 9:13 AM

I can't reproduce your error. What is the content of your cell that produces the error?

Posted by Tom on April 12, 2001 9:56 AM

Here is an example of the content of a cell that triggers the error:

Overview
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx manages the pension assets of xxxxxxx xxxxx employees. The office manages, using active and passive investment styles, portfolios that include separate mid-cap, large-cap, non-U.S., private equity, fixed-income, and indexed portfolios. xxxx defines mid-cap stocks as those with market caps between $250 million and $5 billion and large-cap stocks as those with market caps greater than $5 billion.

Strategy
The actively managed portfolios include separate mid- and large-cap value/growth portfolios that select stocks based on prior and relative performance. The office has a quantitative model that screens for new investment ideas, but it uses fundamental analysis to select stocks based on both growth and value characteristics. xxxx will vary between growth and value approaches depending upon industry sector and market conditions. For its non-U.S. holdings, the office uses the Morgan Stanley EAFE Index for its country allocation selection.

Supplemental Information
xxx was established xxxxxxxxxxxxxxx by the xxxxxxxx State Legislature. (xx)

I want to replace certain text strings (such as "
") with line breaks.

Thanks again!

Posted by Tom on April 12, 2001 10:01 AM

SORRY!... I just realized that what I say I am trying to replace is not showing up in my message, because I am trying to replace HTML tags! The tags show up in the comment box but by the time you see it in the message, the text is already formatted. You may have to copy it from the Comments box itself and paste it into Excel from there.

Posted by Emile P. on April 12, 2001 5:32 PM

This formula seems to work. Perhaps you can use it in your macro.
=SUBSTITUTE(A1,"
",CHAR(10))

Posted by Emile P. on April 12, 2001 7:12 PM

I think this works :
ActiveCell.Value = Application.WorksheetFunction.Substitute(ActiveCell, "
", Chr(10))

Posted by Tom on April 13, 2001 8:28 AM

Emile - it works! I can't thank you enough for your help thus far. My only remaining question is how do I modify it to apply not just to the active cell, but to an entire selection (i.e. an entire column). I tried to hack through a few combinations but couldn't get it to work.

Posted by Tom on April 13, 2001 10:59 AM

I GOT IT!

Emile - I GOT IT! Basically, I used the code you recommended and figured out how to put it in a loop, so it looks like this:

Sub ReplaceTags2()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value = Application.WorksheetFunction.Substitute(c, "
", Chr(10) & Chr(10))
Next
End Sub

Thanks a million. I wouldn't have gotten this without you.

Tom