Firstly, make a copy of your worksheet to practice on.
Then, use the Substitute function.
=SUBSTITUTE(F5,"*",",") - except instead of the asterisk, hit ALT-Enter.
(this forces a line break)
The formula results will have the char(10) replaced with a comma.
If there are multiple instances of the line breaks in your text, leave the last argument in the SUBSTITUTE function out,
That way, all instances will be replaced.
Next, do Copy>Paste Special>Values.
Then go to Data>Text to Columns. Click the Commas button, Finish.
That comma is the separator for the csv format. CSV = Comma separated value. ANd the comma is actually the separator for a new column. (you can split the cell into different collumns using the Text to Columns option)
THen you need to change the text from one row/several columns to one column/several rows which is pretty simple as well.
I am however a little confused. I am under the impression that in you example:
F5 is the Range, "*" is something to do with the Alt-Enter and "," is replacing something with a comma.
Is this correct?
I am however, still confused with how this removes the Alt-Enter and displays the data onto different rows.
If you could give me a little more direction, I would be grateful.
Hi, Kieron -- here's what I did, based on Hugh's answer above:
In the top cell of the column next to the column that I wanted to separate (call it "B2"), I entered the following: =SUBSTITUTE(A2,CHAR(10),*); [that's not really an asterisk: it's the "alt-0149" dot] Then I simply copied that formula all the way down to the bottom row. That gave me a column in which all the "hard returns" from A2 were now represented by the Alt-0149 dot. Then I used Text to Columns with the dot as my separator (this keeps things from getting screwy if you actually have commas in your cells)