Enter 'carriage return' in find and replace dialog in Excel 2008

[JoWillmott]

Posted this elsewhere, but think this is the right place for it, sorry for
double posting...

I have data that I would like to store in an excel spreadsheet. It's
currently in a Word table. The problem is that each line has some cells that
split over several lines. If I copy and paste it as is, the data split over
lines get put into separate rows. I have partly solved this problem by
replacing the paragraph mark (^p) in Word with a tilde (~). Now the rows are
as I want them. But I want to replace the tilde in Excel 2008 with a carriage
return. I know that to enter carriage returns manually I use Alt + Enter. But
I cannot work out how to express that in the Find and Replace dialog. I have
tried searching for answers to this question, but have had no luck so far. If
anyone can help I would be very grateful.
Many thanks,
Jo

 

[Bob Greenblatt]

Posted this elsewhere, but think this is the right place for it, sorry for
double posting...

I have data that I would like to store in an excel spreadsheet. It's
currently in a Word table. The problem is that each line has some cells that
split over several lines. If I copy and paste it as is, the data split over
lines get put into separate rows. I have partly solved this problem by
replacing the paragraph mark (^p) in Word with a tilde (~). Now the rows are
as I want them. But I want to replace the tilde in Excel 2008 with a carriage
return. I know that to enter carriage returns manually I use Alt + Enter. But
I cannot work out how to express that in the Find and Replace dialog. I have
tried searching for answers to this question, but have had no luck so far. If
anyone can help I would be very grateful.
Many thanks,
Jo

Well, you really made it hard for yourself by choosing a tilde. Replace in
Excel does not work the same way it does in WORD. (Why not is quite another
subject.) Anyway, in Excel a tilde is wild card character delimiter. Also in
Excel, you can not use a formula to change the contents of a cell, (you need
a macro to do this), you can ONLY generate a value for the cell containing
the formula. So to accomplish what you want you will need an intermediate
column, and a spare cell. In a spare cell somewhere, let's say c1, enter the
formula =char(13). This will put a single carriage return character in that
cell. Now, assuming your data is in column A, and Column B is the temporary
column, enter this formula in B1: =SUBSTITUTE(A1,"~",$c$1). Then fill this
down for as many rows as you need. Now, column B contains the text as you
want it, so copy it, and select, A1 and then Edit-Paste Special and select
values. Now the updated text without the formula is replaced in column A, so
you can now delete column B, and clear the contents of C1.

 

[JoWillmott]

That's great thank you Bob.
I had one problem, so thought I would explain how I worked round it in case
other people want to do this in the future. Excel didn't like the last bit of
the process - that is pasting the values into the column which had the old
version of the data.
It said 'Excel requires the merged cells to be the same size'. I have no
idea why, as far as I know there were no merged cells.
Anyway, my workaround was to cut and paste special the values on top of the
new column (ie replacing the formulas with the values) and then delete the
old column.
Thanks again for your help!
Jo