Convert line endings when copying from excel to word

Question

Is there an elegant / correct way to deal with CRLF line endings when copying from Excel to Word?

We have some text stored in a database that uses \r\n (i.e. CRLF) for new lines. We use a tool^[1] to query the database and pull the values into Excel. It looks fine in Excel but if we then copy a cell into Word there are two line breaks where there should only be one.

For example the string This is line #1\r\nThis is line #2. looks fine in excel but if we copy from Excel and paste into Word it's 3 lines long.

I've got a macro that removes the CR but it's a bit of a nasty hack and I'd rather not push it out to all users if there is a better solution.

Sub UpdateLineEndings()
'
' UpdateLineEndings Macro
'
ActiveCell.Value = Replace(Selection.Text, vbCr, "")

End Sub

---

[1] We're useing Sharperlight but I'm sure there are lot's of similar tools out there.

Answer 1

Thanks for that @tim-williams I had been hoping there was a way to do it when copy / pasting (a bit like the "keep text only" option).

As you said, I have to do a replace somewhere, I ended up doing it in the SQL of the Sharperlight data model. The column was text, so I cast it as varchar and then did the replace.

REPLACE(CAST({_Table.Alias}.COMMENTS as varchar(MAX)), CHAR(13)+CHAR(10), CHAR(10))

If you want to make your comment as an answer I'm happy to mark it as correct.