Removing Consecutive Line Breaks

[FrankenBob]

Morning,

Apologies if this has already been asked, but I couldn't find anything related.

I am trying to replace instances of double line breaks with a single line break.

So far I am trying the following:

=IFERROR(SUBSTITUTE(A1,CHAR(10)&CHAR(10),CHAR(10)),A1)

However, I get the results below (in 'B1'). I am looking for my formula to remove the additional carriage return in Cell 'A1' (so the result should look like 'A2') or if there are no double carriage returns return the cell unaltered.

Any help would be greatly appreciated.

Cheers

 

[Vidar]

You don't need the IFERROR part. The cells in column B must be formatted with the "Wrap Text" button.
SUBSTITUTE(A1,CHAR(10),CHAR(13))

 

[FrankenBob]

You don't need the IFERROR part. The cells in column B must be formatted with the "Wrap Text" button.
SUBSTITUTE(A1,CHAR(10),CHAR(13))

Thanks for the quick reply, unfortunately; though a little more elegant, your formula returns the same as mine, even with the 'Wrap Text' button enabled.

Unless I am doing something wrong?

I need to preserve instances of a single carriage return.

Thanks for any help.

 

[Vidar]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Hello1


Hello2
[/TD]
[TD]Hello1
Hello2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My result looks like the table above. (The lines below or above, depending on the cell alignment, are empty)

 

[Rick Rothstein]

This formula will handle up to 12 consecutive Line Feed characters (if you need to handle more than that, let me know)...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,REPT(CHAR(10),5),CHAR(10)),REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),3),CHAR(10)),REPT(CHAR(10),2),CHAR(10))