Deleting "unused" line breaks

[seleseped]

I have a worksheet in which a cell has 4 lines of data. Each bit of data is put on a new line within the cell using CHAR(10). Sometimes only 2 of the lines are TRUE and therefore only 2 text lines appear within the cell. However, the CHAR(10) function still inserts line breaks between the empty lines. How do I get Excel to skip the line breaks which do not have any data that follow them? I've tried the CLEAN, SUBSTITUTE, TRIM, FIND, REPLACE functions to no avail.

Example: The output if all data within the cell is TRUE based on the formula =A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1 looks like this:
Apple
Banana
Orange
Peach

However, if B1 and C1 are not true, the output looks like this (2 blank links between apple and peach):
Apple



Peach

How do I get it to look like this (the blank lines are omitted)?
Apple
Peach

Many thanks in advance

 

[Kenneth Hobson]

=substitute(a1&char(10)&b1&char(10)&c1&char(10)&d1,char(10) & char(10),char(10))

 

[seleseped]

Thank you very much. That helps a lot. Now a more advanced question regarding the same problem . . . How do I delete unused line breaks when the blank lines are variable (as in the examples below)?

Example 1 (all lines appear)
apple
banana
orange
peach

Example 2 (blank lines 1 and 4)

banana
orange


Example 3 (blank lines 2 and 4)
apple

orange


Example 4 (blank lines 1 and 3)

banana

peach

Etc.

Again, thanks in advance.

 

[Kenneth Hobson]

In that case, I would use a UDF.

'=withoutblanklines(A1,B1,C1,D1)
Function WithoutBlankLines(cell1 As Range, cell2 As Range, cell3 As Range, cell4 As Range) As String
  Dim a() As String, i As Integer, c As Range
  
  For Each c In Union(cell1, cell2, cell3, cell4)
    If c.Value2 <> "" Then
      i = i + 1
      ReDim Preserve a(1 To i)
      a(i) = c.Value2
    End If
  Next c
  WithoutBlankLines = Join(a, vbLf)
End Function

 

[seleseped]

Again, many thanks. There's NO way I would have figured that out!