Problem copy+pasting CR+LF into an excel spreadsheet need soft paragraph (Alt+Enter)

Question

I'm trying to copy a table that is created in our software into an excel spreadsheet.

Some of the title headers in our application are too big to fit in a column so they are separated by a #13+#10 (CR+LF) so they sit on the next line. e.g.

Strain  SpikeConc  Spike
        ng/g       dpm/g
-------------------------
Blah    20.0       50.1
Blah2   22.1       60.2

However, when this is copied into excel we get a strange thing happening. The CR+LF is interpreted as (you guessed it) a new line request. Hence we get something that looks totally wrong. e.g.

Strain  SpikeConc
ng/g    Spike
dmp/g
-------------------------
Blah    20.0       50.1
Blah    22.1       60.2

It is interpreting the CR+LF after the SpikeConc incorrectly and creating a new line in a new cell instead of creating a soft paragraph like you would get if you pressed Alt+Enter and giving you a new line in the same cell.

Anyone got any ideas how to encode a soft paragraph rather than a new line?

I've tried using just CR (#13) on it's own and just LF (#10) on it's own but they both have the same behaviour.

I believe there are some unicode characters

LS: Line Separator, U+2028

PS: Paragraph Separator, U+2029

but I can't seem to find how to encode them into the table in my App.

PS We're using Delphi6 (don't ask)

Answer 1

As mentioned in the comments, this is the function we use to strip the CR's. Either this or enclosing the string in double quotes suffices to have a soft return in excel.

function RemoveCr(const Value: string): string;
  var
    I, J: Integer;
  begin
    J := 0;
    SetLength(Result, Length(Value));
    for I := 1 to Length(Value) do
      if Value[I] <> #13 then
      begin
        Inc(J);
        Result[J] := Value[I];
      end;
    SetLength(Result, J);
  end;

Answer 2

That's Brilliant. If I put it like this

"SpikeConc #13+#10 ng/g" 

it displays it correctly in excel like this

SpikeConc
ng/g

Thanks. That was driving me crazy.