Add Newline(s) in a Cell

[Emily]

Hi All,

I'm writing output to an Excel file. I have to write all of the
differences into a single cell. Now I've got an output that looks like
this (in a single cell):

The number of text objects differ on page 1: 12; 10 | The number of
text objects differ on page 1: 17; 15 | The number of text objects
differ on page 1: 17; 15 | The number of text objects differ on page 1:
16; 14 | The number of text objects differ on page 1: 17; 15 | The
number of text objects differ on page 1: 18; 16 |

As you can see, it's humanly readable. I want to make it look like
following:

The number of text objects differ on page 1: 12, 10
The number of text objects differ on page 1: 17, 15
The number of text objects differ on page 1: 17, 15
The number of text objects differ on page 1: 16, 14
The number of text objects differ on page 1: 17, 15
The number of text objects differ on page 1: 18, 16

I don't know how to make it look like this. Because, if I add a a
newline ("\r\n") after each difference, this will be split into 6
separate lines - They won't be in the same cell anymore. Any advice?

Thanks!
-Emily

 

[Guest]

Emily, try using a linefeed instead of a carriage return-linefeed (the ascii
character 10, same as the constant vbLF) to achieve the desired impact. See
example:

Public Sub CR_In_Activecell()
Dim rng As Range

Set rng = ActiveCell
rng.Offset(0, 0).Value = "Using chr(10)"
rng.Offset(0, 1).Value = "line 1" & Chr(10) & "line 2" & Chr(10) & "line
3"
rng.Offset(0, 2).Value = "line 1" & vbLF & "line 2" & vbLF & "line 3"
' same thing as previous line
End Sub


Regards,
Bill

 

[NickHK]

Emily,
Whilst I'm all for conservation of resources, is it worth packing much data
into a single cell in the first place ? It will make any form of
check/analysis/correction much more difficult.
I would may be spread each line over 4 columns :
"The number of text objects differ on page"<>1<>12<>10
You can always combine/format for presentation or reporting.

Unless you have a good reason for packing the cells like this ?

NickHK