Pasting data with line returns, line returns not initializing?

[ruinedelf]

Hiya, got a bit of a question! I have some data that is copied and pasted around. However, for some reason, line returns aren't processed until the cell formula is actually entered: Even selecting the cell and copying it around doesn't trigger the line break.

Is there any way of forcing all cells in a worksheet to re-initialize so all line breaks are processed? Ideally I would like a VBA script for this as part of my automation setup, but I figured I'd first try and find out what's causing this to happen.

Thanks!

 

[ruinedelf]

For more info, I've been using a VBA script to detect line returns and replace them with spaces:

Sub ReplaceLineReturns()
    Dim targetRow As Integer
    Dim cell As Range

    ' Set the target row where you want to check for paragraph breaks
    targetRow = 61 ' Change this to your desired row number
   
    ' Loop through each cell in the specified row
    For Each cell In Rows(targetRow).Cells
        ' Check if the cell is not empty and contains a paragraph break
        If Not isEmpty(cell.Value) Then
            If InStr(1, cell.Value, vbLf) > 0 Then
                ' Replace paragraph breaks with spaces
                cell.Value = Replace(cell.Value, vbLf, " ")
            End If
        End If
    Next cell
End Sub

It works when the line break has taken effect, but not when it hasn't (it works in Line Returns 2.png, not in Line Returns.png). A bit of an interesting thing. Another funny thing is that "actioning" the line break can actually be undone (Ctrl+Z). Not sure what's actually going on here.

 

[Alex Blakenburg]

Where the line return doesn't seem to work. What happens when you go turn Wrap Text on in the cell ?
Assuming that works then in your macro that copies in the data you can use something like this:

Range("A2:C10").WrapText = True

 

[ruinedelf]

Turning on Wrap Text wraps the text without processing the line return. Please see attached image, thanks!

 

[ruinedelf]

This is still confusing me, I've tried a script that appends and then removes a space to all the cells, but while it does do that, it doesn't actually initialize the cell to trigger the line return. Any other ideas?

 

[ruinedelf]

Just to test, here's the MiniSheet in XL2BB format. The issue doesn't carry over directly, instead the line break is kind of ignored by the BBCode and the parse completes properly. I've attached pictures of what the original data in Excel and the BBCode look like. Note that Word Wrap is on for all cells.

Upload Test Sheet.xlsx
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Date analysed	TRH C6 - C9	TRH C6 - C10	vTPH C6 - C10 less BTEX (F1)	Benzene	Toluene	Ethylbenzene	m+p-xylene	o-Xylene	Naphthalene	Total +ve Xylenes	Surrogate aaa-Trifluorotoluene	Date extracted	Date analysed
Sheet1

 

[ruinedelf]

Found the solution. The carriage return is not CHAR(10) as I thought it was, it was CHAR(13). Targeting the right special character solved it!