I know how to put a carriage return into Excel with alt-enter.  

What I want to do is be able to insert one in the replace function,
preferably.  In this case, I'm moving a lot of data from a Word table
(yuck) into Excel.  The Word table contains many carriage returns.  I
can replace them with "XXX" using find and replace (replace ^p with
XXX) and then the cells will move over into Excel properly rather than
creating more than one row each.

But then, how can I replace XXX with a carriage return in Excel?  It
doesn't appear to recognize '^p' the way that word does.

Is there a way to put a line return into a formula?  (vbCr doesn't seem to work).

Help!

tamkins-ga:

You are absolutely correct in thinking that there is no way to get a
line feed inserted into your text using the standard Excel Replace
dialog. The problem is not the Replace function; the problem is the
dialog box, which was not designed to allow non-standard characters.

You can, however, accomplish what you are looking for using a macro
program. For the purposes of this Answer, I will assume that you are
using either Excel 2000, Excel XP/2002, or Excel 2003. This approach
is not valid for older versions of Excel such as Excel 97 or Excel 95.
For those ones, you will need to take a similar approach, but
rewritten as an Excel Basic macro instead.

In Excel 2000-2003, open up the spreadsheet with your data, then
please go to Tools>Macro>Macros... (this is based on Excel XP, the
exact path may differ if you are not using Excel XP).

In the Macros... dialog, type a new new name for the Macro (such as
MyReplace), then click the 'Create' button.

In the Visual Basic for Applications window that appears, paste the
following text between the Sub MyReplace() line, and the End Sub line:

    Cells.Replace What:="XXX", Replacement:=Chr(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End Sub

Close the Visual Basic for Applications window.

In the Macro dialog, highlight the macro you just created, then click
the Options... button.

In the Macro Options dialog that appears, click in the shortcut key
field, then select your keyboard shortcut (for this, just hold down
Shift and type R, to give you a keyboard shortcut of Ctrl-Shift-R for
this macro).

Click OK to return to the Macro dialog, then Cancel to return to Excel.

Now, assuming that you used 'XXX' to mark where the line feeds should
go, simply start the macro using the keyboard shortcut (Ctrl-Shift-R
in this example), and all occurences of 'XXX' will be replaced with
the line-feed character (the Chr(10) in the command above).

Please let me know if any of this is unclear, or if you run into
problems using it. Simply use the 'Request Clarification' button above
for this.

Hope this helps,

aht-ga
Google Answers Researcher

---

Request for Answer Clarification by tamkins-ga on 23 Mar 2004 07:59 PST

I'm delighted that this can be done so easily.  However, when I try
it, I get a compile error, "Named argument not found".  The hilited
argument is SearchFormat.  Here is how the thing looks:

Sub ReplaceXXXwithCarriage()
Cells.Replace What:="XXX", Replacement:=Chr(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

The about box says I am using Visual Basic 6.0, and I'm using Excel 2000.

---

Clarification of Answer by aht-ga on 23 Mar 2004 08:52 PST

Whoops, sorry, that argument was added in XP, it isn't available in
2000 after all. My bad!

Fortunately, it is irrelevant to your actual requirement, so just
delete the ", MatchCase:=False, SearchFormat:=False, _" part of that
line. Also delete the "ReplaceFormat:=False" on the next line. This
will leave you with the following command:

    Cells.Replace What:="XXX", Replacement:=Chr(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows
    End Sub

Please let me know if you run into any other problems.

Regards,

aht-ga
Google Answers Researcher

---

Request for Answer Clarification by tamkins-ga on 23 Mar 2004 09:21 PST

This time I get a run-time error '1004': Formula is too long.

I tried only running it on a tiny subset of my worksheet in case the
worksheet was just too big for some reason (?) but I got the same
thing.  Here's what it looks like now:

Sub ReplaceWithCarr()
    Cells.Replace What:="XXX", Replacement:=Chr(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows
End Sub

---

Clarification of Answer by aht-ga on 23 Mar 2004 09:44 PST

It looks like you are running up against one of the limits within MS
Excel 2000. While a cell can conceivably contain up to 32,767
characters (of which only the first 1,024 will appear in the cell),
when you use any of the built-in functions (such as the Replace
function), you are limited to working with the first 1,024 characters.

Is this the case? If so, is there any way for you to shorten the cell
contents in Word before moving them to Excel?

---

Clarification of Answer by aht-ga on 23 Mar 2004 09:45 PST

Incidentally, do the contents of the cells happen to have '='
characters (equal sign) in them?

---

Request for Answer Clarification by tamkins-ga on 23 Mar 2004 10:25 PST

I removed all cells with more than 1024 characters and ran it again. 
It will do a few rows, but then it gives me the same error again. 
(The spreadsheet is 383 rows by 17 columns, and the longest cell is
900-something characters.)

---

Clarification of Answer by aht-ga on 23 Mar 2004 10:42 PST

I have found reference to a similar issue where the user had to go
down to 900 characters for his function to work:

http://dbforums.com/t972650.html

Microsoft does not document this side of their product very well, so I
cannot say with certainty what impacts the exact number of characters
that can be present in the cell. Noting, of course, that only the
first 1024 characters can be displayed in any cell anyway, is there
any possibility that you can limit the cell contents to at most 900
characters before running the Replace function? As well, use a shorter
proxy character, such as the '^' character, or some other unique
character instead of "XXX".

Excel is powerful, but also very limiting when working with text.
Again remembering the 1024 character display limit, what else can be
done with the text so that you can perhaps use multiple cells to
represent each cell of the Word table?

aht-ga
Google Answers Researcher

---

Clarification of Answer by aht-ga on 23 Mar 2004 11:02 PST

tamkins-ga:

Through trial-and-error, I have determined that the maximum content
size that the Replace function will work with, is 901 characters. So,
in order to accomplish what you are hoping to do using this 'simple'
approach, you will need to work only with cells that are 901
characters max. A line feed is a character.

Please let me know if there are cells where you absolutely must have
greater than 901 characters, and cannot simply use multiple rows to
represent the contents.

aht-ga
Google Answers Researcher

---

Clarification of Answer by aht-ga on 23 Mar 2004 11:26 PST

tamkins-ga:

I think I've found a solution (this problem intrigued me, I absolutely
had to find a way to solve your problem!).

From this interchange on the Experts Exchange, I found some code
similar to what you are hoping to do, that completely bypasses the
Replace function and its built-in limitations:

http://www.experts-exchange.com/Applications/MS_Office/Q_20597672.html

I will post customized code for you in a few minutes.

aht-ga
Google Answers Researcher

---

Clarification of Answer by aht-ga on 23 Mar 2004 11:51 PST

Drawing upon the example from the Experts Exchange forum (
http://www.experts-exchange.com/Applications/MS_Office/Q_20597672.html
), I created the following function and subroutine that replaces every
occurence of the tilda character (~) with a line feed.

===================================================

Public Function ReplaceCharacter(ByVal s As String, ByVal oldchar As
String, ByVal newchar As String) As String
Dim sVal As String

'Set the late binding objects
Dim rPart As Object

sVal = s

Set rPart = CreateObject("VBScript.RegExp")
rPart.Global = True
rPart.IgnoreCase = True
rPart.Pattern = oldchar

'replace all occurences of the pattern c with the
sVal = rPart.Replace(sVal, newchar)

Set rPart = Nothing

ReplaceCharacter = sVal

End Function

Sub MyReplace()
Dim s As String


  On Error GoTo MyReplaceAbort
  
  Application.DisplayStatusBar = True
  Application.StatusBar = "Replacing Characters..."
  Application.ScreenUpdating = False

  For Each c In Selection.Cells
  c.Value = ReplaceCharacter(c.Value, "~", Chr(10))
  Next c
  
MyReplaceAbort:

  Application.ScreenUpdating = True
  Application.StatusBar = False
  Application.DisplayStatusBar = True

End Sub

===================================================

To use this function, first copy and paste the above code into the
Visual Basic Editor as you have done before.

Next, prepare your text in Word by replacing all paragraph marks with
the tilda character (~). Then, import it into Excel.

Now, select the cells that you want this function to work on; in my
testing, I filled 383 x 17 cells with 2000 characters each (consisting
of 20 repeated cycles of 99 characters and one tilda, resulting in
twenty tildas per cell). I selected these cells, then ran the
'MyReplace' macro. On a P4/1.4, it took 2.5 minutes to process to
completion.

I hope that THIS solves the problem!

aht-ga
Google Answers Researcher

Wow!  I wish I could afford to tip more for what was surely more than
$5 worth of work, in the end.  Thanks so much for your help.

tamkins-ga:

I'm glad that the final solution helped; I can't even begin to imagine
trying to tackle the job manually, for a 383x17 table! Thank you very
much for the tip!

Regards,

aht-ga
Google Answers Researcher